Excel cell having some invisible character

2
votes

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

5
excel
copy the cell, paste into a hexeditor, see what that mysterious char is. – Marc B
and take a look here for example: stackoverflow.com/questions/30176376/… – KekuSemau
I am not good in vba the link that you sent, do I select the column in question press alt+f11 and paste it there in a module? – user3777207
Use the method for Find & Replace but for CHAR(10) characters. They can be created with Ctrl+J and replaced with nothing. They are linefeeds. If you have XL2013 use =unicode(mid(A$1, row(1:1), 1)) and fill down 11 rows to see what you have actually got. I'm betting linefeed (which isn't even Unicode). If not XL2013 use =code(mid(A$1, row(1:1), 1)) and hope it isn't Unicode. – user4039065
@pnuts LENB shows the length as 10 as well. – user3777207

5 Answers

2
votes

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?

1
votes

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.

1
votes

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.

1
votes

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.

0
votes

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.