Preceding a number by zeros, that is still a number
 

I get send spreadsheets with supplier numbers on them, i.e. 256. i wish to
change these to 0000256, as all our account numbers contain 7 digits.
I know i can do this by either putting a ' before the number or changing the
cell to text, however i wish to change all numbers in a click.
I discovered i could do this by using the format cell custom function. This
indeed changes all my supplier codes to a 7 digit number. However this number
i.e. 0000256 is displayed as 256 in the formula bar, which does not help me
as i need to use these supplier number against other sheets using vlookup. as
it is not seen as a number, or seen as the number 256, vlookup does not
recognise it as 0000256 but as 256 so i cannot use the function.
Please help

Hi

Into another column, enter the formula like (the example is for row 2,
assuming your original number in cell A2):
=TEXT(A2,"0000000")
Copy the formula down, and then replace with values (PasteSpecial Values).


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"Michele" wrote in message
...
I get send spreadsheets with supplier numbers on them, i.e. 256. i wish to
change these to 0000256, as all our account numbers contain 7 digits.
I know i can do this by either putting a ' before the number or changing
the
cell to text, however i wish to change all numbers in a click.
I discovered i could do this by using the format cell custom function.
This
indeed changes all my supplier codes to a 7 digit number. However this
number
i.e. 0000256 is displayed as 256 in the formula bar, which does not help
me
as i need to use these supplier number against other sheets using vlookup.
as
it is not seen as a number, or seen as the number 256, vlookup does not
recognise it as 0000256 but as 256 so i cannot use the function.
Please help