Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting to text - apostrophe does not show
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting to text - apostrophe does not show
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 "Marina Tuttle" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting to text - apostrophe does not show
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 Marina Tuttle wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting to text - apostrophe does not show
Typo alert...
Make sure that the look up range includes enough columns: =VLOOKUP("0345N9",A1:B25,2,FALSE) (A1:a25 changed to A1:B25) Mike H wrote: 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 "Marina Tuttle" wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show apostrophe / single quote in cell | Excel Discussion (Misc queries) | |||
Formatting alignment - can I show text upside down? | Excel Discussion (Misc queries) | |||
Apostrophe vs Text format | New Users to Excel | |||
why is an apostrophe in front of text | Excel Discussion (Misc queries) | |||
Cell has an apostrophe and is text | Excel Discussion (Misc queries) |