Apostrophe vs Text format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DTTODGG - 12 Apr 2006 14:22 GMT

Hello-

I'm attempting a VLOOKUP.

The lookup value is a field that has the little green triangle in the upper
left corner of the cell (like when you put an apostrophe before a number) ie
10054

The col_index_nbr in another file is a number 10054 (without the little
green triangle).

When I attempt a VLOOKUP - it does not work.
If I re-type the col_index_nbr so that is begins with an apostrophe, the
lookup works. But my file is huge and I can't re-type all of these numbers.
If I reformat them to "text", it still does not find a match.

Please help.

Reply to this Message

Dave Peterson - 12 Apr 2006 14:30 GMT

Your key column in the lookup table is numeric, but the value that you're
matching up is Text.

You can coerce those Text values to number values by:
selecting an empty cell
edit|copy
select the range of text numbers
edit|Paste special|check add

> Hello-
>
[quoted text clipped - 13 lines]
>
> Please help.

Signature

Dave Peterson

Reply to this Message

DTTODGG - 12 Apr 2006 15:04 GMT

Thank you for your quick response, Dave!

You are correct, the key column is numeric and the matching column is text.

The problem is, the matching column is text because it is a 13 digit number

:-( Therefore, I can't use your suggestion.

So, would I rather coerce the key column to have the little apostrophe? I
don't know how to do this. It seems so simple. I can manually change the key
column to have an apostrophe and then everything works. If I convert it to
text it does not.

The question is: what's the difference between the little green triangle and
formating a cell to "text"? And how can you get little green apostophes in
many rows?

VLOOKUP
B2 (where B2 has the little green triangle (because the numbers range from 5
to 13 digits)
filename and valid data (where col A is a 5 digit number and col B is a 13
digit number with a little green triangle)
1020600060054 (where this number has the little green triangle)
False

Thank you again.

> Your key column in the lookup table is numeric, but the value that you're
> matching up is Text.
[quoted text clipped - 22 lines]
> >
> > Please help.

Reply to this Message

Dave Peterson - 12 Apr 2006 17:26 GMT

I don't understand why 13 digits would represent a problem.

And if =vlookup(value(... worked, then I think that the other suggestion would,
too.

> Thank you for your quick response, Dave!
>
[quoted text clipped - 52 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

bpeltzer - 12 Apr 2006 15:09 GMT

Alternately, if you continue to get imported data of this form, you could
embed the conversion from text to number in the lookup formula. Instead of
=vlookup(a1...), =vlookup(value(a1)...)

> Your key column in the lookup table is numeric, but the value that you're
> matching up is Text.
[quoted text clipped - 22 lines]
> >
> > Please help.

Reply to this Message

DTTODGG - 12 Apr 2006 15:33 GMT

Brillant! So, simple, so beautiful!

I REALLY do appreciate EVERYONE who contributes on this site. It has been a
GREAT learning experience for me.

Thank-you EVERYONE.

> Alternately, if you continue to get imported data of this form, you could
> embed the conversion from text to number in the lookup formula. Instead of
[quoted text clipped - 26 lines]
> > >
> > > Please help.

Reply to this Message