How to extract partial data from Excel tables: filtering and function methods

Key points of this article

To find the entire row that satisfies the condition, use automatic filtering or the FILTER function; to find a value by number, use XLOOKUP or VLOOKUP.
When the cell contains text such as "Quantity: 120 pieces", use column sorting or the MID, LEFT, and RIGHT functions to extract the numbers.
Back up the original table before starting, and check whether the table header is standardized and whether the numbers are saved as text.
Visible rows are copied after filtering; #N/A appears in the formula result, most likely because the search value is inconsistent with the source data format.
After fetching the data, compare the number of rows and spot-check three to five items, which is more reliable than just looking at the results.

Many people have an Excel sheet with thousands of rows in their hands. The leader only needs the records of a certain department, a certain type of customer in the customer list, or a separate number in a description text. Manually copying line by line is slow and easy to miss. This article is written for people who need to extract the desired rows or values from within the table. It does not involve identifying tables from PDF.

After reading this article, you will know how to determine whether you want to retrieve the entire line, a certain value, or a number in the text, and then choose the corresponding method. The article includes the usage of commonly used filtering and search functions, preparation before starting, how to check the results, and what to do when the data is still in PDF.

How to extract the data you want in the table? One sentence conclusion

As long as the entire row that satisfies the condition, use filter; as long as you want to find a value by keyword, use the search function; as long as the number in the cell, use the column or text function. The table below allows you to choose according to your own situation.

your situation	Recommended practice	Reason
To fetch all rows that meet the criteria, such as the records of a department	Automatic filtering, or advanced filtering	There is no need to write formulas, and the results are directly visible, which is suitable for viewing at one time.
To save the filter results as a new table	Copy the visible region after filtering, or use the FILTER function	Only visible rows are taken when replicating; FILTER can be updated with source data, but a newer version of Excel is required
Find a value by number, such as a name by job number	XLOOKUP, with VLOOKUP in the old version	Match by keyword, and it is not easy to make mistakes when the order of the rows of the source table changes.
In the cell, there are words like "quantity: 120 pieces". Take a number.	Array, or MID, LEFT, RIGHT combined with LEN	Cut out the numeric part in a fixed position or delimiter
Multiple conditions need to be met at the same time.	Overlay multi-column conditions in filtering, or combine conditions with multiplicative signs in FILTER	The conditions are superimposed one by one, leaving only the rows that meet at the same time.

Before you start: Confirm these points first

Take a few minutes to check the following items before starting to save yourself the trouble of rework.

Whether the data is already in the table. Formulas can only calculate the contents of cells. If the data is still in PDF, export the table to Excel format first and then process it. OmniDoc's PDF editor supports exporting to Excel. In other cases, you can use a general online conversion website, but pay attention to whether privacy and formatting are preserved.
Whether the header is standardized. The first row should be the column names, without merged cells or blank rows in between. Otherwise, the filter range will be broken at blank lines and the area referenced by the function will be incomplete.
Excel version. XLOOKUP and FILTER require newer versions. If the software version is older, use VLOOKUP instead, or a combination of INDEX plus MATCH, which will have the same effect.
Format of numbers and numbers. Lookups and calculations often failed when numbers were saved as text; numbers starting with 0, such as "001", lost leading zeros in some cases. Take a look at the alignment and formatting of these columns before extracting.
Back up the original table first. Copy a worksheet or file and then operate it. The original data will not be overwritten when taking the value, and you can go back and compare after making a mistake.

Steps to extract data using filter and search functions

The following steps apply to a structured form. If you only need one of these operations, skip the irrelevant steps.

Select any cell in the header and turn on the filtering function. A drop-down arrow will appear on the right side of each column name.
Click the drop-down arrow of the target column, first cancel "Select All", and then check only the required values. Filter conditions for text, numbers and dates can be set separately.
When you need to limit multiple columns at the same time, repeat the previous step in another column. The conditions will be superimposed one by one, and only the rows that meet the conditions at the same time will be retained.
Check the row number on the interface and the count at the bottom to confirm that only records that meet the conditions are left in the table.
Select the visible result area to copy and paste to a new worksheet. In the filtered state, only visible rows will be copied, and hidden rows will not be carried over.
If you want to get the value by number, enter the search formula in the blank column, for example =XLOOKUP (find value, search column, return column). It is recommended to put the search value in a separate cell and then reference it to facilitate subsequent modification, and then fill it down.
If you want to extract numbers from text, you can use =MID (text, starting position, length) to intercept the numbers at a fixed position. You can also select the column and use the column splitting function to split it according to separators or fixed widths.
If you don’t want to use desktop office software, you can also process the same form in the UniCell spreadsheet of the OmniDoc desktop client. The download address is download.omnidoc.top. The operation idea remains the same: first set the conditions, then get the value, and finally check.

# check item by item after completion

After the extraction, check the results item by item, especially the data to be given to others for use.

Check item	What do you think?	What if there's a problem?
Is the number of rows correct?	Compare the number of filtered rows with expectations, and look at the count at the bottom of the interface	If there are too many lines, check whether there are residual filter conditions; if the number of lines is too small, check if there are any extra spaces or full-width characters in the conditions.
Whether the value obtained is correct	Three to five items were randomly selected and compared with the original table one by one	When the result is misplaced, check to see if the sequence number of the returned column is miscalculated, or find out if the column is in the first column of the zone (required by VLOOKUP)
Can figures participate in the calculation?	Sum the result column, or see if the numbers are right-aligned by default	The left alignment of numbers is usually text, which can be converted with the VALUE function, or resorted
Whether the formula is wrong or not	Check to see if there is an error value such as # Nramp A	# NCMA mostly means that the corresponding value cannot be found. Check whether the lookup value is consistent with the format and space of the source data.
Whether the original table has been changed	Compare with backup copy	Operate in a copy, use undo when a miscorrection is found, or reset the value from a backup

# frequently asked questions (FAQ)

1. Copy and paste after filtering, why is the number of rows wrong?

In most cases, the selection is not limited to visible rows at the time of replication, or filter conditions remain in the new table. Confirm that the table is filtered before copying, check for filter arrows after pasting into the new table, and recheck the number of rows.

2. What should I do if I return # NumberA from VLOOKUP?

Nswap An indicates that no match was found. First check whether there are extra spaces between the lookup value and the contents of the source table, and whether the format of the number and text are consistent. Also make sure that the lookup column is in the first column of the lookup area, otherwise VLOOKUP will not match correctly.

3. The old version of Excel does not support FILTER, can it still be counted according to conditions?

Sure. When you select the whole row according to the condition, you can copy the visible row after automatic filtering; take the value by number, and use the combination of VLOOKUP or INDEX plus MATCH. These methods can also be used in older versions.

4. How does the data become a table that can be processed by functions in PDF?

First export the table in PDF to Excel format. OmniDoc's PDF editor supports exporting to Excel. If the PDF is a scanned piece, it can be identified by OCR first. After export, be sure to check whether the numbers and text are complete against the original file, and errors may occur in the recognition and conversion process.

# Summary

How to extract the desired data from the table, the key is to first make it clear whether you want to take the whole row, a value or the number in the text, and then choose the corresponding method. Screening is suitable for keeping lines according to conditions, search function is suitable for taking values by number, sorting and text function are suitable for taking out numbers. Finally, don't forget to compare the number of lines and spot check values, and make sure the results are correct before using them.

Open OmniDoc and process your form file