Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup text conversion issue
I am getting inconsistent results from a vlookup
I am querying on field X which contains text -- or at least it looks like text. could be a combination of numbers and letters. -- usually numbers that have leading zeros I have a search table with the same values -- or what looks like the same values. When I do my vlookup -- to make it find the correct answer, i have to go into my query table cell and hit return. I've tried formatting both sets of columns as General, Text, etc. and can't seem to overcome. The data is imported from another program. I think it has something to do with the data in my query table not have an EOL character ??? Short of going into every cell and hitting RETURN/ENTER -- is there someway to get both my query cells and search cells to be formatted the same? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup text conversion issue
This could be due to extra blanks and/or non-printable characters. Look at
TRIM/CLEAN functions (for both query and search table) to see if they can get the data consistent. "PamKT" wrote: I am getting inconsistent results from a vlookup I am querying on field X which contains text -- or at least it looks like text. could be a combination of numbers and letters. -- usually numbers that have leading zeros I have a search table with the same values -- or what looks like the same values. When I do my vlookup -- to make it find the correct answer, i have to go into my query table cell and hit return. I've tried formatting both sets of columns as General, Text, etc. and can't seem to overcome. The data is imported from another program. I think it has something to do with the data in my query table not have an EOL character ??? Short of going into every cell and hitting RETURN/ENTER -- is there someway to get both my query cells and search cells to be formatted the same? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup text conversion issue
PamKT wrote:
I am getting inconsistent results from a vlookup I am querying on field X which contains text -- or at least it looks like text. could be a combination of numbers and letters. -- usually numbers that have leading zeros I have a search table with the same values -- or what looks like the same values. When I do my vlookup -- to make it find the correct answer, i have to go into my query table cell and hit return. I've tried formatting both sets of columns as General, Text, etc. and can't seem to overcome. The data is imported from another program. I think it has something to do with the data in my query table not have an EOL character ??? Short of going into every cell and hitting RETURN/ENTER -- is there someway to get both my query cells and search cells to be formatted the same? Try to clean your imported data. For example if your table start from A2 type in an empty column, say from E2 this formula: =clean(trim(A2)) for text entries or also =value(clean(trim(A2))) for numerical entries or =if(iserror(value(clean(trim(A2)))),clean(trim(A2) ),value(clean(trim(A2)))) that can manage both text and numerical entries. Than drag across the rows and columns. At the end you could copy and past special, values, to eliminate the added columns... -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup where text not exact | Excel Discussion (Misc queries) | |||
Conversion to Text file format error | Excel Discussion (Misc queries) | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Vlookup datatype(?) issue | Excel Worksheet Functions |