Vlookup text conversion issue

[Guest]

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?

 

[Guest]

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.

 

[Franz Verga]

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