How do I add an apostrophe in front of numbers?

[Jhensien]

I have a long list of numbers that start with zero's that I need to use a vlookup for. I need the zero's in front of the number for the vlookup and I know if I add an apostraphe in front, the zeros won't vanish and my vlookup function will work. I have tried playing around with changing the number format to text, general, special, custom, etc. to keep the zeros in front (didn't work because each sheet I am pulling from is different and is not responding to these changes) What I need is a formula I guess to quickly put an apostrophe in front of a bunch of cells - if a formula exists?

Can anyone help?
Does anyone know what I am talking about?

Kind regards,

Jeff

[Sisyphus]

Hi,

The apostrophe converts the number to a text string. You can achieve the same result by changing the cell format to text. On the other hand, if your cells are not formatted as text already there wouldn't be any zeros that you can look up. So, the problem is to format the cells to text. Look at it this way, a cell that contains the number 7 could show up on the screen as 007. But when you look at the actual contents in the formula bar you can see that there are no zeros. The cell only holds a 7. So, putting an apostrophe in front of it will also not give you any zeros. all you get is '7 where 7 is no longer a number.

Now, since you do have cells with zeros in front a conversion took place. Numbers were converted to text. You can force this with this formula:

Code: Select all

=TEXT(E3,"000")


if E3 holds the number 7, the cell with the formula will show 007. However, this is now a formula based on the original number in E3. I don't know if you can use it to look up the zeros. If not, you can now convert the formula to a text string. Select the cell with the formula in it, press Copy, then Paste Special and select the radio button for "Values". The cell now holds 007, with the leading zeros and it is a text string, not a number.

I hope this helps somehow.