Excel cell has an invisible character

Question

I was trying VLOOKUP between two different Excel sheets, but it always returned #N/A. 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(), but it still shows 10. Then I used the following answer on "https://stackoverflow.com/questions/9578397/remove-leading-or-trailing-spaces-in-an-entire-column-of-data" :

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.

Answer 1

If you know the cell with the non-visible characters, click on it. (Make it active.)

Click in the formula bar.

Click "Home" key or use the arrow keys to go left/up as far as possible.

If the cursor is NOT on a visible character, use the Delete key (Windows keyboard - delete ahead) until a visible character is there. (Mac keyboard, use right arrow key until you get to a visible character, then Backspace ("delete") what's before.)

Then use the "End" key to get to the end of text and use the Backspace key (good on Mac & Windoze) until you get to the end of the visible text that you want to keep.

For good measure, while at the end, use the down-arrow key to go past any other lines (line feeds/invisible chars), and Backspace (delete to the left) until you get to the last visible char you want.

The problem is finding the cells with those unwanted "invisible" characters, as some cells may not be text, instead, a real number, like 7, which displays as a 7 (ctrl-G or "rings the bell" if text) but is really just a number 7, and you don't want to eliminate those.

BTW, the visible/printing/keyboard characters are a continuous sequence of decimal 33-126, hex 21-7E, according to my ASCII table. This is for an English US keyboard. Likely a macro can be made to search for all textual cells containing characters outside of the visible character range.

Answer 2

Building on the other answers, here's how I quickly found the rouge character.

1. Narrow the widths of the cells to the right of the cell you are investigating to make the results easier to read.
2. Insert a row above the row containing the cell under investigation.
3. Assuming the cell under investigation is at A2, enter the following forumula to the right of that cell =UNICODE(MID($A2,COLUMN()-1, 1)) and the following forumla in the cell above the cell with the forumla you just entered =UNICHAR(B2). (Change the A2 and B2 to suitable values if the cell you are checking is not at A2).
4. Cell B1 should show the first character of the cell under investigation.
5. Copy cell B2 to the cells to the right of it, and copy cell B1 to the cells to the right of that.

You can now see the Unicode value of each character in your cell. Spaces should all be 32. If any space is another value, such as 160, copy that character and then use it to globally replace all instances of that character in your spreadsheet with a space.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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?