Chemical ID conversion

[Ellen]

Hello,
I have a field of CAS# numbers, which identify chemicals.
These numbers can exist either as a nine digit number or a
number with hyphens between the first and second right
hand digits and between the third and fourth right hand
digits. In the second version the leading zeros are
dropped.

For instance,

10213-78-2 to become 010213782
17201-15-9 to become 017201159
126-00-1 to become 000126001
977139-89-1 to become 977139891

I have the version that has hyphens and I'd like to
convert them to the nine digit version without hyphens.

I can readily remove all the hyphens. I only then need to
add leading zeros so that they're nine digits long.

Thank you in advance.

Ellen
..

 

[Jason Morin]

=TEXT(SUBSTITUTE(A1,"-",""),"000000000")

HTH
Jason
Atlanta, GA

 

[Bob Phillips]

an alternative to ponder

=RIGHT("000000000"&SUBSTITUTE(A1,"-",""),9)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Jason,
You're beautiful! Thanks!

-----Original Message-----
=TEXT(SUBSTITUTE(A1,"-",""),"000000000")

HTH
Jason
Atlanta, GA

.