Formatting to text - apostrophe does not show

[Marina Tuttle]

I got a bunch of numbers that look like this 0345N9 and I want to have the
apostrophe Not the quote in front: '0345N9. I have done the regular stuff
of going to data, text to columns, apostrophy as text qualifier and then
click the botton "text."

The cell will show the green triangle as usual on the upper left corner but
the apostrophe is not there to be seen. Then when I want to do a vlookup it
will not find it because it misses the apostrophe.

I tried also to concatenate the cell to an apostrophe, it will not work.

Is there another way?

 

[Mike H]

Marina,

Ignore the apostrophe and do your lookup as if it wasn't there and it will
work

=VLOOKUP("0345N9",A1:a25,2,FALSE)

Mike

 

[Dave Peterson]

Do you see that apostrophe in the cell or only in the formula bar?

If it's only in the formula bar, you can hide it via:
Tools|options|transition tab|Uncheck Transition Navigation keys

But having that apostrophe (prefixcharacter) isn't enough to break the
=vlookup() formula--well, unless you can see it in the cell.

If you can see it in the cell, you could remove it (just reenter the value to
test). Or you could remove it in your formula:

=vlookup(mid(a1,2,len(a1)),sheet2!a:b,2,false)

But this is only if that apostrophe is part of the data--not a prefix character.

I'm guessing that it's something else causing the trouble.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

 

[Dave Peterson]

Typo alert...

Make sure that the look up range includes enough columns:

=VLOOKUP("0345N9",A1:B25,2,FALSE)
(A1:a25 changed to A1:B25)