3

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.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
user3777207
  • 61
  • 1
  • 1
  • 8
  • copy the cell, paste into a hexeditor, see what that mysterious char is. – Marc B Sep 16 '15 at 19:57
  • 1
    and take a look here for example: http://stackoverflow.com/questions/30176376/debugging-whitespace-in-vba/30176846#30176846 – KekuSemau Sep 16 '15 at 20:02
  • 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 Sep 16 '15 at 20:05
  • 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. –  Sep 16 '15 at 20:06
  • You can stick that mysterious character, that is def not a Char(160) since that would get sub'd out in your formula, by using `=code("")`. That will spit out the decimal, or "Char()" code for that character. Then you can address it properly with `=substitute` or whatever. – JNevill Sep 16 '15 at 20:08
  • @Jeeped It doesn't sound like Unicode since it if it was and OP is seeing 9 characters then `len` would do something like return 20 rather than 10 (assuming it is in an encoding that uses 2 bytes per Unicode character). – John Coleman Sep 16 '15 at 20:15
  • Maybe copy the formula `=LEN(TRIM(CLEAN(SUBSTITUTE($A$1,CHAR(ROW()),""))))` down 256 rows in column B and see where numbers less than 10 pop up. – John Coleman Sep 16 '15 at 20:18
  • @JohnColeman - If I throw `selection = "Jeep" & ChrW(8203) & "ed"` into the VBE's Immediate window and go back to the XL2010 worksheet and use `=LEN(A1)`, I get an return of 7 despite the Unicode zero-length space inserted. The [CODE function](https://support.office.com/en-us/article/CODE-function-C32B692B-2ED0-4A04-BDD9-75640144B928) reports it as **63**. –  Sep 16 '15 at 20:26
  • @Jeeped Interesting. I was thinking that if the entire string came from an external Unicode source then that would likely be reflected in how all characters are encoded – John Coleman Sep 16 '15 at 20:29
  • So the length of all the cells in that column is not fixed, The one that I picked up to test had 9 characters but it can vary. – user3777207 Sep 16 '15 at 21:17
  • 1
    @pnuts LENB shows the length as 10 as well. – user3777207 Sep 16 '15 at 21:19
  • You can sometimes get a similar-looking issue to this when there's a tab character at the start of the cell, but the text still displays left-aligned. – Myles Jun 20 '18 at 09:22
  • 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. – Pal Mar 27 '19 at 06:51

6 Answers6

2

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?

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • If it's 32 then this is not even a question. – Excel Hero Sep 16 '15 at 20:16
  • Excel Hero: I followed your instructions and the first cell B1 appears blank and the code C1 is 63. How does that help? – user3777207 Sep 16 '15 at 21:14
  • OK. Now do it again, but this time use a slightly different formula in column C: `=UNICODE(B1)` What is the code then? – Excel Hero Sep 16 '15 at 21:20
  • 1
    For the cell that showed 63 (the mysterious blank cell) the unicode is 54. I would also like to point out that the next cell having alphabet C had code as 67 but the unicode for that cell is 54 as well. – user3777207 Sep 17 '15 at 00:11
  • Ok I finally got it.. not quite.. but I was able to get what I wanted to do.. I had used power query which is an excel add in to import a bunch of text files (8000+) into excel. I noticed that if I right click on the file content column which is originally represented by value "Binary" and click on convert to text and then load the data the length of the cell in question is 9 which is accurate. Thank you all. – user3777207 Sep 17 '15 at 02:44
1

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.

Jason Carpenter
  • 11
  • 1
1

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.

Hashima Khan
  • 11
  • 1
1

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.

kynan
  • 13,235
  • 6
  • 79
  • 81
matt2103
  • 311
  • 3
  • 13
0

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.

Tim Rogers
  • 426
  • 5
  • 14
0

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.

PReinie
  • 57
  • 1
  • 4