Excel can't find the number? What should I do if the text and numbers are inconsistent?

Key points of this article

For numbers that look the same, one may be text and the other may be a number. The search function will determine that they are not equal.
Use ISNUMBER, ISTEXT and LEN to check the real type and character length of two columns to quickly locate the problem.
To convert text to numbers, you can use column splitting, VALUE or multiplying by 1. To convert numbers to text, you can use TEXT or concatenate empty strings.
Long numbers and leading zeros must be set to text before entering, otherwise Excel will overwrite or lose the last number.

Many people search for "How does the Excel search function correspond to text numbers" and encounter roughly the same situation: using VLOOKUP or XLOOKUP to lead data from one table to another, the result returns #N/A, or a blank is returned. After comparing the two tables, the number "1001" is obviously the same, but I can't find it.

This situation is very common. The problem is often not that the formula is written incorrectly, but that the data on both sides "look the same but are actually different": one side is text and the other side is numbers; or there are spaces or full-width characters hidden in the text. This article is written for people who often do data verification and report summary. After reading it, you will be able to judge which category the search failure belongs to, use several formulas to find the problem cell, and unify the format of the two columns.

Can't find it, how can I make the text and numbers match? One sentence conclusion

First confirm whether the types of the two columns are consistent, and then convert one column to the type of the other column; if there are spaces or invisible characters, clean them first and then compare. The table below corresponds to several common situations.

your situation	Recommended practices	reason
The search value is a number, and the source table number is text (there is a small green triangle in the upper left corner of the cell)	Convert the source table number to a number, or use VALUE conversion in the auxiliary column	Numbers and text are treated as different values when compared
There is text on both sides, they look the same but they don’t match.	Use LEN to check length and TRIM to remove spaces.	The leading, trailing or middle spaces are not displayed, but are involved in the comparison.
Numbers with leading zeros, such as "00123" and "123"	Unify both sides as text, or use TEXT to supplement the digits	Number format will automatically remove leading zeros
ID number, bank card number and other numbers exceeding 15 digits	Save both columns as text and then compare	Excel's numerical precision is only 15 digits, and the last few digits will become 0
The number is mixed with full-width digits, such as "1001"	Use ASC function to convert to half-width, or replace manually	Full-width and half-width characters are encoded differently

First determine what kind of problem it is

Before changing the formula, use the table below to compare the symptoms and confirm the cause.

symptoms	Common causes	How to confirm
Returns #N/A, but the numbers look the same to the naked eye	Text and digital types are different	In the blank cells, enter ISNUMBER(A2) and ISTEXT(A2) separately, and measure one cell for each column
There is a small green triangle in the upper left corner of the cell	The numbers are stored as text	Select a cell, and the prompt next to it will indicate "Number stored in text form"
Most banks can find it, but a few cannot	There are spaces at the beginning or end or in the middle, but invisible characters	Comparing the number of characters obtained with LEN(A2) to the naked eye, the extra spaces appear
The last few digits of the long number become 000	More than 15 digits lost in digital precision	Check the numbers in the editing bar to see if all the ends are zeros, and confirm whether the data comes from import
The lead zero is gone	The numeric format removes the leading zero	Compare the content in the editing bar with the cell display
The numbering width is larger than other rows	Full-width characters are mixed in	Check if the characters are as wide as the Chinese characters, or use LEN to compare lengths

Inspect and fix each item one by one

Handle them in order from easy to difficult, and most problems can be solved in the first four steps.

First, check the search formula itself. The last parameter of VLOOKUP is written as FALSE (or 0), indicating an exact match; If you write TRUE or omit, you may return a close value or even an incorrect result. XLOOKUP matches exactly by default, but you need to ensure the number of rows in the lookup area matches the return area.
Test types with ISNUMBER and ISTEXT. Enter ISNUMBER(A2) in the blank column, and the result is TRUE for the number and FALSE for the non-number. Then perform the same test on the other column. If one side is a number and the other is text, the problem is inconsistent types.
Check length with LEN. Enter LEN(A2) and compare the result with the number of bits visible to the naked eye. If there are one or two extra characters, usually a space.
Clean up spaces and invisible characters. Enter TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), ""))) in the auxiliary column. CHAR(160) is a common continuous space when copying data from web pages or other systems. After confirming the results are correct, selectively paste them as values and then use them for searching.
Uniform types. To convert text to numbers, you can use the column split function to set the column format to regular at the last step of the wizard, or enter VALUE(A2), --A2 or A2*1 in the auxiliary column. Numeric to text can be done using TEXT(A2, "0") or A2&"". When you need to fill in the leading zero, use TEXT(A2, "00000"), and fill in the number of bits according to the numbered length.
Back up the original data before batch conversion. Select the entire column containing the text number, use the column split function, and in the final step, set the column format to Regular or Text to complete the entire column conversion. Note that this operation will overwrite the source data, so it is recommended to perform it on the replica.
Handle full-width characters. In the Chinese version of Excel, you can use the ASC function to convert full-width characters to half-width, for example, ASC(A2); When the quantity is small, manually modifying directly using the Find Replace function is faster.
Use auxiliary columns to verify results. After conversion, count the number of incorrect rows that still return the error, confirm that #N/A has disappeared, and then pass the result to downstream users.

Long numbers and leading zeros need to be handled in advance

Numbers like ID card numbers or bank card numbers with more than 15 digits will be entered as numbers in Excel, causing the last few digits to become zeros. Lost numbers in the file cannot be retrieved using formulas and must be re-imported from the original data. The correct approach is to set this column as text format before entering or importing.

The situation with leading zero is similar. If the number "00123" is saved in numeric form, it will appear as 123. If both sides need to keep leading zeros, they should be saved uniformly as text and kept the number of bits consistent.

How to Prevent It from Happening Next Time

When exporting data from the system or other software, first check the formatting of the numbered columns, and set them uniformly to text or numbers before merging the tables.
ID number, bank card number, and other long numbers should be set to text format before entry.
When copying data from a web page or chat window, prioritize using Paste as Optional as Values, reducing the chances of hidden spaces and formatting being imported into tables.
Before merging the two tables, randomly check a few rows and use ISNUMBER and LEN to confirm that the type and length match.
Be cautious when looking for IFERROR in the formula's outer package. It can make #N/A disappear, but it also masks match failures, causing the summary numbers to be too small to detect. The cause should be identified first, and then a decision on whether fault-tolerant handling is needed.

❓ Frequently Asked Questions (FAQ)

1. The cell looks like a number, but the ISNUMBER returns FALSE. Why?

These are usually numbers in textual form, commonly seen in data exported from web pages or systems. Text is aligned to the left by default and numbers to the right, which can serve as a quick basis for judgment.

2. Are VLOOKUP and XLOOKUP matching rules the same?

Both treat text and numbers as different values. The difference is that XLOOKUP defaults to exact matching, while VLOOKUP requires the last parameter to be set to FALSE for accurate matching. No matter which function you use, the fixing method is to use a unified type and clean up spaces.

3. I've switched types, so why can't I find it?

There are three common reasons: only one column is converted, and the other column remains in the old format; Residual spaces or invisible characters not cleared; The lookup area does not use absolute references, so the range shifts when pulling down the formula. Problems can be identified one by one by checking LEN and ISNUMBER.

4. Why did the last digits of the ID number become 0?

This is because Excel's numeric accuracy is only 15 bits. Set the entire column to text before entering to avoid this. Deformed data cannot be restored using formulas and must be re-imported back into the original file.

5. Is it okay to use IFERROR to hide #N/A?

It is not recommended to treat it as a remedial measure. After hiding errors, there may appear no errors on the surface, but data that fails to match will be treated as null or zero in the calculation, making errors more likely. It is recommended to first identify the cause and unify the format before considering whether fault tolerance is needed.

📝 Summary

Excel cannot find numbers, most of the time due to inconsistencies between text and number types, or mixed spaces and full-width characters. First use ISNUMBER and LEN to locate the problem, then unify the type and clean up the characters. Using the same Excel lookup function to match text to numbers can solve most problems. When important data is involved, remember to back up the original table first.

Open OmniDoc and process your table file