' in numbers and Pivot tables

  • Thread starter Thread starter Nav
  • Start date Start date
N

Nav

I have a spreadsheet with multiple rows of the same ID
number. The ID number (when you click on the cell) has
a ' before the number.

I have created a pivot table to condese this data into
something more meaningful. (which works) However I do not
see any ' in front of the ID number.

I now need to use vlookup to lookup a value with the same
ID as I have on another sheet. But when I use the vlookup
formula, I get #N/A. However if I manually add ' in front
of the ID in the main sheet it looks it up. There are
thousands of rows, but I do not fancy putting an ' in
front of them all.

I have tried to use MID to see if that would get rid of
the ', but it just gets rid of the first number in the ID
column.
I have also tried to use =CONCATENATE("'",AB4)and use
vlookup on that, but it does not work either.

Does anyone have any ideas?

Thanks in advance for your help.
 
The ' is an internal marker to say that the characters which follow it
are to be treated as text (and, crucially, not numbers - even if they
are digit 0 - 9).
It isn't part of the entry as such.
If you want to change a large number of numbers into text, you can do
it by using Format|Cells|Number and select Text. Use it on blocks and,
by linking with Ctrl, do it on separate blocks.
 
Back
Top