Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't vlookup read newly entered data from a source file? | Excel Discussion (Misc queries) | |||
Print Visable Data Only | Excel Worksheet Functions | |||
Print Visable Data Only | New Users to Excel | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
Excel 2000 VLOOKUP returns #N/A unless press F2 return on source? | Excel Worksheet Functions |