Vlookup (Source data has a non-visable character) Excel

C

Credit Law Guy

I've got a vlookup challenge that I'm not sure how to overcome. I must
manually click on the source cell which is a seven digit number and move the
cursor to the right of the number (XXXXXXX)|(Click Delete) then the vlookup
will work.

I've tried to copy and paste values only. I've tried to use the Left or
Right command to just bring over the 7 characters and trim. There appers to
be something to the right that needs to be moved or deleted.

Any ideas? Thanks,
 
F

FARAZ QURESHI

1. There might be a space;
2. The color of the font might be white;
3. Some custom format might have been applied;

1. Select all (Ctrl+A and again Ctrl+A)
2. Press Ctrl+Shift+1
3. Select No fill for back ground
4. Select automatic for font color.

Hopefully it worx.

Regards
 
K

Ken Johnson

I've got a vlookup challenge that I'm not sure how to overcome. I must
manually click on the source cell which is a seven digit number and move the
cursor to the right of the number (XXXXXXX)|(Click Delete) then the vlookup
will work.

I've tried to copy and paste values only. I've tried to use the Left or
Right command to just bring over the 7 characters and trim. There appers to
be something to the right that needs to be moved or deleted.

Any ideas? Thanks,
Click to expand...

Try the CLEAN function.

Ken Johnson
 
T

T. Valko

to the right of the number (XXXXXXX)
I've tried to use the Left or Right command to
just bring over the 7 characters and trim.
Click to expand...

Those functions all return TEXT. So, if XXXXXXX is a number:

=LEFT("XXXXXXX ",7) returns XXXXXXX as a TEXT string. TRIM also returns a
TEXT string.

You need to figure out what that 8th chatacter is.

Assume A1 = XXXXXX<some unseen 8th char>

=CODE(RIGHT(A1))

A common unseen character is a space which will return a code number of 32,
another is a non breaking html space which will return code number 160.

Once you find out what those characters are you can use Edit>Replace to get
rid of them.

Select the range of cells in question
Goto the menu Edit>Replace
Find what: For the char 32 space just hit the spacebar, for the char 160
space hold down the ALT key and use the *numeric keypad* and type 0160. You
will not be able see either one of these characters in the Find what box but
you'll see that the cursor has moved.
Replace with: Nothing. Leave this empty
Replace all
 
Top