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

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,

--
Credit Law Guy

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

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


"Credit Law Guy" wrote:



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,

--
Credit Law Guy

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

On Oct 7, 11:49 am, Credit Law Guy wrote:
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,

--
Credit Law Guy

Try the CLEAN function.

Ken Johnson

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

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.

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 EditReplace to get
rid of them.

Select the range of cells in question
Goto the menu EditReplace
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

--
Biff
Microsoft Excel MVP


"Credit Law Guy" wrote in message
...


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,

--
Credit Law Guy