ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

How to get rid of invisible characters when using Vlookup?

Updated on September 27, 2010

Imagine you have the table shown below...

ZIP Table

And imagine you want to make a simple Vlookup. Write the ZIP code to get the STATE.

So you write a VLOOKUP: =VLOOKUP(A2;!$A$2:$C$6;3;0)

The problems arise when the values you are looking for (2282 and 2162) are on the source table but you get #N/A errors.


What is the problem?

Don't give credits to your eyes, if Excel shows a #N/A error is because the value is not there.

So then what to do?

There are invisible characters you don't see on the ZIP Table shown above.

A trick I use is to concatenate a character to the left and right of the cell so I can catch visually that a problem exists.


See the ZIP CHECK column below...

Now that you aware that spaces exist, you need to get rid of them.

The typical solution to this problem is to use a TRIM, SUBSTITUTE function or even the REPLACE command but there are cases where these functions don't work.

This is one of those cases where spaces remain after the application of those functions so I will show to you how to get rid of those invisible spaces....

Substitute the spaces by using this formula:


What does this formula do?

It substitutes spaces by an astheric (*) two times.

Copy and paste values on the left column. After you do it the ZIP CHECK column now must show something like this:

Delete ZIP CHECK and ZIP NO SPACES columns.

Now your Vlookup column is ready for a perfect lookup.

And yes, your Vlookup is perfect now!


    0 of 8192 characters used
    Post Comment

    No comments yet.