Interesting VLookup quirk

[normpam]

A1 B1 C1 D1
Norman 123 Elm st Norman =Vlookup(C1, $A$1:$B$10,2,false)

Just as a simple example. Problem is, it's not finding the value in C1 as 'Norman'. I've checked for spaces - not an issue. I did = Len() on both cells; they are the same. If I use = EXACT, they are different.

I noted that one of them was imported from another program and came in a a merged cell. Would this throw off the VLookup? And if so, is there a CHAR code I could look for and use SUBSTITUTE to fix the issue?

 

[normpam]

Use this VBA code to find the ASCII code of every single character in your string.
Simply select the cell you want to analyze, and run the code.

Sub GetASCIICodes()

    Dim cd As String
    Dim i As Long
   
'   Loop through each character of activecell and get ASCII code
    For i = 1 To Len(ActiveCell)
        cd = cd & "Character " & i & ": " & Mid(ActiveCell, i, 1) & " - " & Asc(Mid(ActiveCell, i, 1)) & vbCrLf
    Next i
   
'   Return analysis
    MsgBox cd
   
End Sub

Thanks... will try it out. Pretty intriguing, huh?

 

[Joe4]

Thanks... will try it out. Pretty intriguing, huh?

I see this quite often with data that comes off of the web or out of other programs. They often have special characters in them (something 32, sometimes not).
So things that look like they should match, often times actually do not.

If you run that code I gave you on both the cells that you think should match, you should be able to easily spot the differences (which will tell you why they do not actually match).

 

[normpam]

Works. Thanks. Turns out that the 'space' was really char 160! What's interesting is that when I tried to find the char code for a space, all if found was CHAR 32

 

[Joe4]

Works. Thanks. Turns out that the 'space' was really char 160! What's interesting is that when I tried to find the char code for a space, all if found was CHAR 32

ASCII 32 is the typical normal space that you get when you manually enter a space in Excel.
ASCII 160 is a special non-breaking space that often comes from the web.
They may look the same, but are not.

See: Difference between breaking and non breaking space ascii characters!)

 

[Joe4]

Note that you can Find and Replace all those non-breaking spaces with normal spaces, by using this simple method that Rory describes here:

Special Character - Remove Nonbreaking Space

Hi i have downloaded some data to excel. it has got leading Nonbreaking Spaces as shown below CLEANSERS LOTIONS/MILKS/CREAMS OTHER Can anyone help me in removing this i tried find and replace method in excel to remove space and also tried trim function but it didn't work thanks

www.mrexcel.com

If you do that to get rid of all those characters, then your VLOOKUP formula should work as expected.

 

[SpillerBD]

Let me see if I can simplify this. It's really pretty straightforward.
In a particular cell there is the entry K Buildings. There is another sheet that has the exact same entry, but a VLookup doesn't work. If I do a 'Find' for a space, Excel tells me that there is NO space in the cell that has the first entry of K Buildings. I've seen this before, and found that using a SUBSTITUTE formula to get rid of the CHAR 32 (space) character will work. However, when I use the SUBSTITUTE formula on this cell, it turns out that the 'space' between K and Buildings, is not only not a space, but is not the result of CHAR 32 either. What else could be lurking in the cell that makes it appear that there is a space in it?

You can use MID(CODE() to yield the specific ASCHII number of the character. You may have a CODE(160) which is the non-breaking space character.

 

[Joe4]

You can use MID(CODE() to yield the specific ASCHII number of the character. You may have a CODE(160) which is the non-breaking space character.

Yes, we already found that using the VBA code I provided, which uses that type of logic.
They already confirmed that the non-breaking space was the culprit in post 13 (maybe you missed the second page of posts?).