Convert stored numbers into a set naming convention

[Sean]

I have a list of around 2000 numbers ranging from 3 to six digits. I need to
convert all numbers to six digits with leading zeros is 345 becomes 000345
and 12345 becomes 012345.

I also need to convert all of those to a set naming convention with a set
prifix letter M and followed by PC001
so 345 becomes M000345PCOO1
AND 12345 BECOMES M012345PC001.

All original numbers are stored as numbers on one column
Can anyone help?

 

[tim m]

I'm not sure how you would do this without a lengthy IF statment that counts
the number of digits in your number. Some of the real experts will
undoubtably have the proper solution for you.

You can get the numbers to show up with the leading zeros by going:
format....cells....custom and then in the type: box type in 000000, this
will get your numbers to show leading zeros however I don;t know how to carry
that formatting over into your formula which would look like this:

="M"&B1&"PC001"

This formula will put the M and the PC001 in the proper places but it seems
to strip the leading zeros even if your number is formatted to have them.

 

[Biff]

="M"&TEXT(A1,"000000")&"PC001"

Biff

I'm not sure how you would do this without a lengthy IF statment that
counts
the number of digits in your number. Some of the real experts will
undoubtably have the proper solution for you.

You can get the numbers to show up with the leading zeros by going:
format....cells....custom and then in the type: box type in 000000, this
will get your numbers to show leading zeros however I don;t know how to
carry
that formatting over into your formula which would look like this:

="M"&B1&"PC001"

This formula will put the M and the PC001 in the proper places but it
seems
to strip the leading zeros even if your number is formatted to have them.

 

[Sean]

Brilliant - works like a dream

="M"&TEXT(A1,"000000")&"PC001"

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Brilliant - works like a dream