How to organize the extracted text into an Excel table

Key points of this article

First check whether there is a unified delimiter (tab, comma, semicolon or fixed space) in the text. The delimiter determines the splitting method.
After pasting into Excel, the data is usually all in column A. Use the column split function to split it according to delimiters, which is faster than manual cutting and less likely to be missed.
When the text comes from PDF or scanned documents, export or recognize the text first and then split it, otherwise the rows and columns will be easily misaligned.
After splitting, check the number of rows, numbers and date format line by line, and confirm that the content of each column is consistent with the original text

Many people copy a large paragraph of text from a Word contract, meeting minutes or web page, and want to organize the names, amounts, and dates into Excel, but find that after pasting, all the content is squeezed into one column. This article is written for people who need to organize the extracted text into a table. It is suitable for Word documents, plain text and content extracted from PDF. How to turn the text into a table after extracting it? The problem usually does not lie in Excel itself, but that the text does not tell the software where to separate columns and where to wrap lines.

As long as you unify the delimiters first and then use Excel's column splitting function to split them, most text can be turned into neat rows and columns in a few minutes. The entire process does not require manual adjustments one by one, nor does it require understanding complex formulas.

After reading this article, you will know how to determine which delimiter should be used for text, how to complete splitting in Excel, and how to check the results after splitting to avoid misaligned numbers or rewritten dates.

How to turn the extracted text into a table? One sentence conclusion

First, use the same symbol between each field in each row of the text, then paste it into Excel and divide the columns according to this symbol. The rows and columns obtained in this way are consistent with the structure of the original text and do not need to be modified one by one.

your situation	Recommended practices	reason
There are already tabs or commas between fields	Paste directly into Excel and select the corresponding separator to separate columns.	The original structure is intact and can be disassembled in one step
There are only spaces between fields, and each paragraph is of different lengths	First replace the consecutive spaces with unified separators, and then divide into columns	The number of spaces is not fixed. Splitting directly by spaces can easily cause misalignment.
Text from Word table	Directly copy and paste the table range into Excel	The row and column structure of the table itself is retained and does not need to be split.
Text from PDF or scan	First export to Word or recognize the text, and then process it according to the above method	Text copied from PDF often has line breaks, headers and footers, and splitting it directly will cause confusion.

Before you start: Confirm these points first

Taking a minute to judge the condition of the text before starting can save a lot of rework.

Where the text comes from. The copied content of Word documents, plain text, and PDFs are processed differently. There are often line breaks and page breaks in PDF, and a field may be split into two lines after copying.
What is used to separate fields. After opening the text, look carefully to see if there are tabs, commas, semicolons, vertical bars, or just spaces between fields. The delimiter determines which option you select in Excel.
Whether each record is on its own line. If a record spans several lines (such as address wrapping), it needs to be merged into one line first, otherwise the table will have extra blank lines and the number of lines will not match.
The content itself has no delimiters. For example, if the address contains commas, using commas to separate columns will separate the addresses. In this case, change to a rare symbol, or only remove the required columns.
Whether to keep the original file. The text will be changed during the editing process. It is recommended to save an original version first so that you can refer to it if you make any mistakes.

Steps to organize Word text into Excel table

The following is an example of a Word list. Each line contains name, department and phone number, with commas separating fields.

Open the document in Word, select all the text that needs to be sorted, and confirm that each record occupies a separate line.
Delete unnecessary blank lines and paragraphs. Empty paragraphs will become empty rows after being pasted into Excel, affecting sorting and filtering.
Use search to replace unified delimiters, such as replacing full-width commas with half-width commas, or replacing multiple consecutive spaces with a tab character, to ensure that only one delimiter is used for the entire text.
Select all the processed text and copy it.
Select the starting cell (for example, A1) in Excel and paste. At this time each record is usually displayed in column A as a whole.
Select the data in column A, use Excel's column splitting function, select split by delimiter, check the delimiter that matches the text, and confirm in the preview that the positions of each column are correct before completion.
Add a header to each column, such as "Name", "Department" and "Telephone", and check whether the first row is mistaken for data.
Save in xlsx format for later sorting, filtering and calculation.

When the text comes from PDF or scanned documents, what should I do first?

When text in PDF is copied directly, problems such as line breaks and mixed headers and footers often occur, so it is safer to sort out the text first and then split it. The file can be opened with the PDF editor of the OmniDoc desktop client (download.omnidoc.top). The scanned document is first subjected to OCR recognition. After the recognition is completed, the content is exported to Word or Excel, and then the delimiters are unified by returning to the above steps.

OCR recognition results need to be checked paragraph by paragraph, especially numbers, amounts, phone numbers, and names, where recognition errors are often concentrated. Sorting them into columns after checking them is correct can reduce the time of repeated revisions later.

Check each item after completion

After getting the form, don't rush to use it. Check it first according to the table.

Check items	How to see	What if there's a problem?
Does the number of rows match?	Compare the number of records in the original text with the number of rows of Excel data, and deduct the header	Find blank or cross-line records and reclassify them after merging
Is the content of each column misplaced?	Randomly check a few lines to see if the name and amount fall in the corresponding column.	Check if there are any extra delimiters and redo the sorting after unification
Is the number rewritten?	Check whether the long number, phone number, ID number and number starting with 0 are complete.	When sorting, format the corresponding column as text, and then import it again.
Whether the date format is correct	See if the date has been changed into a number or other format	Specify the date format when sorting, or set it uniformly after sorting.
Whether symbols and spaces are unified	See if there are full-width half-width mixing, extra spaces or garbled codes	Replace unified search with search before saving
Whether the content is consistent with the original text	Check the amount, name and other key fields against the original text	Go back to the original text and reorganize it after revision. Don't just change Excel.

# Core comparison

After extracting the text and sorting it into a table, there are the following common methods, each of which is suitable for different situations.

Method	Suitable for the scene	Matters needing attention
Paste directly	The text itself is neat, and there are tabs between the columns	It may still need to be sorted after pasting.
Sort after pasting	There is a uniform delimiter between fields	If the delimiter appears in the content of the field, it will result in false disassembly.
Convert to a table first in Word	The law of ranks and ranks is obvious, and only a simple structure is needed.	Uniform delimiters should also be used before conversion
Finishing after PDF export	The text source is PDF or scanned.	Manual verification of line breakage and recognition results is required.

# frequently asked questions (FAQ)

1. What if there is no uniform delimiter in the text?

You can manually add tabs or commas to key locations, or replace fixed field prefixes with delimiters. If the number of characters in each field is basically fixed, you can also choose to sort by fixed width in Excel.

2. After pasting to Excel, the number becomes date or scientific counting, how to change it?

This is caused by the automatic recognition format of Excel. When sorting, select the text format for the corresponding column in the last step, and then import it again. The numbers that have been altered need to be copied back to the original text.

3. Can the sorted form be directly used for sorting and statistics?

Yes, provided that only the same type of content is placed in each column, the header is not repeated and is in the first row, and there are no merged cells in the middle. Otherwise, the sorting results will be confused and the statistics are prone to errors.

4. How to sort out the text copied from the web page?

Web pages are often copied with extra formatting and blank lines. It is recommended that you first paste into a plain text editor to remove the format, then unify the delimiter, and finally paste into the Excel column.

# Summary

How to turn the text into a table after extraction is the key to three things: first unify the delimiters, then paste and separate them, and finally check them item by item. Encounter PDF or scanned pieces, first complete the text export or recognition, and then split. By operating in this order, it is no longer difficult to organize the extracted text into an Excel table.

Open OmniDoc to edit and export documents online