Chemical ID conversion

E

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
..
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conversion of chemical ID numbers 1
Add zeros and remove hyphens 3
Add hyphens 3
Need to add hyphens 3
Problem with Function to Convert Number To Words 9
How to modify function 7
format numbers to letters 1
coverting numbers to words 1

Top