Styjun

I am in the verge of destroying my computer. A task that I anticipated will take a few minutes I am sitting on it since 2 hours. I was trying vlookup between two different excel sheets but it always returned #N/A. So I took one cell and started testing it, I found out the problem is on the lookup workbook (second workbook). The value in the cell is a string of alphabets of length 9. But when I use the =len(A1) formula it shows 10 characters. So I used trim, still it shows 10, then I used this post:

Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

You can also remove the CHAR(160) directly without a workaround formula by

Edit .... Replace your selected data,
in Find What hold ALT and type 0160 using the numeric keypad
Leave Replace With as blank and select Replace All

Still it shows 10 characters, instead of 9. Please HELP

Here is an easy way to find out what your characters are.

Copy your cell with the 10 characters to a new sheet on cell A1.

Select cells B1:B10 and click on the Formula Bar at the top of the worksheet and paste this formula:

=MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Now... in the selected cells you should see one character per cell. One of these will most likely LOOK like a blank cell, but it is not.

Now, select cells C1:C10 and click on the Formula Bar again. This time, paste this formula:

=CODE(B1)

You will confirm this formula differently. Press Control and Enter at the same time.

In column C, you will now see the character codes for each of the characters in column B. What is the code for the cell that looks blank?

I ran into this problem when exporting data from an external website. I tried trim, clean, trim(substitute), clean(substitute), re-pasting into new worksheet, examining the format of the original cell. Nothing was working. So what I did was examined the parts of the string. In my case it was a 16 digit number stored as a string. using the left function, i returned the leftmost 4 characters. Only 3 of the characters were showing. So I just used the right function to return the rightmost 16 characters and that removed the mysterious invisible leading character.

This question and the answers above (or below) guided me through solving a problem I had, so I wanted to throw some upvotes and summarize in it's entirety an answer to the OP's original question:

Step 1: Determine the character code of the mystery character. =UNICODE() used on a single character will give you this code. If you get a #NAME? error, remember to put quotes around the mystery character since it should be entered as a string.

Tip: Convert the font to Webdings to clearly see all the characters. You will notice characters that are [] (example).

Step 2: Substitute the mystery character with a visible character, or delete it entirely using:

=SUBSTITUTE(A1,UNICHAR(x),"y")

Where x is the code determined in step one, y is what you want to replace it with, and A1 is the location of your problem cell.

UNICODE and UNICHAR can theoretically be replaced by CODE and CHAR, but in a quick test that I just performed with ♪, I couldn't get them to work.

There are many alternative methods, especially once you start considering VBA, but the key is in being able to figure out what the mystery character is to begin with.

I have same problem. Copy data from the database and when do vlookup, couldn't find the same text. I tried everything, TRIM, RIGHT, LEFT, LEN but still not working. Until I found a solution but I forgot from where I got it and it works for me. I copy the range I want to lookup and paste in Word as Unformatted text. Then, copy back the text and paste in excel with Unformatted text. After done this, the vlookup works fine. Hope it helps. Thanks.

Thank you @matt2103. That worked for me. Just to add to his points, do not use code() function. Use only unicode(). I had unicode 8206 - an invisible/mystery character causing value error when applying formula in excel.