I have 6000 records of store numbers that are formated as text in column A. Some of the store "text" numbers have 2 characters, some have 3 and others have 4. I need to copy these stores numbers into column D, with a minimum of 4 characters. Those that only have 2 characters need to have 2 zeros added to the beginning of the store number and those that have 3 need to have 1 zero added to the beginning of the store number.

I tried to use the If(lenA2) 4, ("0"&A2),A2) however it only added one zero. How do I get it to add 1 zero if the store number is equal to 3 and 2 zeros if a store number is equal to 2.

Thanks for your help

Regards

Gene Haines

You can use the TEXT function: =TEXT(A2,"0000")

Gene Haines wrote:
I have 6000 records of store numbers that are formated as text in column
A. Some of the store "text" numbers have 2 characters, some have 3 and
others have 4. I need to copy these stores numbers into column D, with
a minimum of 4 characters. Those that only have 2 characters need to
have 2 zeros added to the beginning of the store number and those that
have 3 need to have 1 zero added to the beginning of the store number.


I tried to use the If(lenA2) 4, ("0"&A2),A2) however it only added one
zero. How do I get it to add 1 zero if the store number is equal to 3
and 2 zeros if a store number is equal to 2.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html