Function Problems

Fix Excel VLOOKUP for Numbers Stored as Text with Spaces

Olivia MillerOlivia Miller Nov 20, 2026 868 views

Question details

Clean numeric values stored as text so VLOOKUP can match them despite unwanted spaces or nonprinting characters.

Fix Excel VLOOKUP for Numbers Stored as Text with Spaces
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.
Before you start

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.

Solution 1Recommended

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.

1
Clean the lookup column

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),"").

2
Fill the formula down

Select C2 and drag its fill handle down through C100, or as far as the data in column B extends.

3
Clean the lookup value

In E2, enter the same formula as in C2, but replace B2 with A2. This gives VLOOKUP a normalized numeric value to search for.

4
Run an exact-match lookup

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.

Normalize numeric values in a helper column before VLOOKUP
Return a related value: To return another field, place its corresponding values immediately to the right of the helper column in D, then use =VLOOKUP(E2,$C$2:$D$100,2,FALSE). VLOOKUP searches the first column of the selected range.
Work with lookup data in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 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. 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. 4. Find the value: In F2, enter =VLOOKUP(E2,$C$2:$C$100,1,FALSE), adjusting the range to cover your helper column.
Clean spaces and unwanted characters before matching numeric values.Use familiar spreadsheet formulas including VLOOKUP, TRIM, CLEAN, and SUBSTITUTE.Open and edit Microsoft Excel XLSX workbooks.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

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).