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.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)