Format Cells with too many leading zeros 2

[Jeanie]

Hi.

I have a long list of numbers (actually formatted as text). They should be 9 digits long, but they all have an additional leading zero. Some of the numbers should have 2 leading zeros, but have 3, and some should have no leading zeros but have 1.

Ultimately, what I need is for the numbers to be 9 digits long with the appropriate number of zeros in front. I have found information on line on how to format so that it drops ALL leading zeros, and I know I could do that and then use concatenate to add back the appropriate number of zeros, however, I don't know how to change the format on an existing list - I use the custom format and it doesn't change....but, if I set up an empty column with the format to drop the zeros I am able to type the number in with leading zeros and they don't show up. Obviously, I don't want to re-type all the numbers in the list. And, I'm guessing that there is an easier way to do all of this that I don't know!

Any help you can give is greatly appreciated!

Thanks,

Jeanie

 

[SkipVought]

Hi,

If your "number" is in A1, enter this formula in a new column in row 1...
[tt]
=Text(A1,"000000000")
[/tt]

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[AnotherHiggins]

[COLOR=blue white]=TEXT(VALUE(A1),"000000000")[/color]
and fill down the column.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Jeanie]

Thanks....I love this site and how helpful you all are!

Thanks so much, this seems to work.

Jeanie

 

[SkipVought]

Jeanie,

Keep in mind that "numbers" are often not numbers. Part Numbers, Invoice Numbers, Employee Numbers, etc. are often comprised of numeric characters, but are designed to be IDENTIFIERS rather than ARITHMETIC. You would never do something like this...
[tt]
=INT((EmployeeNbr-2)/7)*7+2
[/tt]
but I often use that very calculation with a DATE to arrive at a Monday Date.

It will save blood, sweat & tears to design your table or list with TEXT for these kinds of fields, so that this TEXT conversion will never be an issue.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}