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<at>tarkon.ee )



"Michele" <[email protected]> wrote in message
news:[email protected]...
>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