Fix Excel VLOOKUP for Numbers Stored as Text with Spaces
Question details
Clean numeric values stored as text so VLOOKUP can match them despite unwanted spaces or nonprinting characters.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A lookup column contains numbers stored as text with trailing spaces, nonprinting characters, or other unwanted characters.
- Observed behavior
- An exact-match VLOOKUP may not find values that appear to match because their underlying characters or data types differ.
Identify the lookup values and the column to search. The formulas below assume the lookup value is in A2 and the values to search are in B2:B100; adjust the references to fit your sheet.
Normalize numeric values in a helper column before VLOOKUP
Use this method when numeric lookup values contain extra characters or are stored as text.
CLEAN removes character codes 0 through 31, TRIM handles ordinary spaces, and SUBSTITUTE removes the additional character codes listed in the formula. The double minus converts the cleaned result to a number.
This method is intended for numeric values. Do not convert identifiers with meaningful leading zeros to numbers, because those zeros will be lost.
In an empty cell C2, enter =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(B2)),CHAR(127),""),CHAR(129),""),CHAR(141),""),CHAR(143),""),CHAR(144),""),CHAR(157),""),CHAR(160),"").
Select C2 and drag its fill handle down through C100, or as far as the data in column B extends.
In E2, enter the same formula as in C2, but replace B2 with A2. This gives VLOOKUP a normalized numeric value to search for.
In F2, enter =VLOOKUP(E2,$C$2:$C$100,1,FALSE). The formula returns the matching cleaned value or #N/A if no match is found.

Clean lookup values and use VLOOKUP in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, normalize numeric lookup values in a helper column, and run an exact-match VLOOKUP.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
- 2. Create a helper column: Select an empty cell beside the lookup data, such as C2, and enter the cleaning formula shown above for the value in B2.
- 3. Fill and normalize: Drag the C2 fill handle down the lookup column. In E2, use the same cleaning formula with A2 as its reference.
- 4. Find the value: In F2, enter =VLOOKUP(E2,$C$2:$C$100,1,FALSE), adjusting the range to cover your helper column.

Frequently Asked Questions
Why does VLOOKUP return #N/A when the values look the same?
The values may have different data types or contain hidden characters. Clean both the lookup value and the lookup column, then use FALSE as the fourth VLOOKUP argument for an exact match.
Does CLEAN remove every nonprinting character?
No. CLEAN removes character codes 0 through 31. Use SUBSTITUTE with CHAR for additional characters, such as CHAR(127) or CHAR(160), when they appear in your data.
Can I use this numeric cleaning formula for product codes with leading zeros?
Not if the leading zeros are meaningful. The double minus converts text to a number and removes leading zeros. Keep those codes as text and clean both sides without the double minus.
How can VLOOKUP return a related value instead of the cleaned number?
Place the related values in the column immediately to the right of the cleaned lookup values. For helper values in C and return values in D, use =VLOOKUP(E2,$C$2:$D$100,2,FALSE).




