How to get rid of invisible characters when using Vlookup?



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.

VLOOKUP ZIP Table

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.

=CONCATENATE("*",A2,"*")

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:

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),"*"),"*","")+0

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!

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working