Add hyphens

[Guest]

Hello,
I have a lot of CAS#s, which come in two formats. One as nine digit
numbers, one with hyphens. I'd like to convert the nine digit numbers to
ones with hyphens. Hyphenated version would lob off the leading zeros and
add hyphens at the third and first from the right. Examples are as such:

000050000 to become 50-00-0
001234567 to become 1234-56-7
123456789 to become 123456-78-9
000123456 to become 123-45-6

How can I convert these numbers that are in a table?

Thanks for your help,
Ellen

 

[Guest]

Try this:

[NewValue] = (CAS \ 1000) & "-" & Format(((CAS Mod 1000) \ 10), "00") & "-"
& (CAS Mod 10)

Where:
- CAS is the field with your CAS#


Take care
Mauricio Silva

 

[John Spencer (MVP)]

This should work for you

Format(Val([Cas]),"#-##-#")

 

[Guest]

Thanks, John!!!!

Ellen

This should work for you

Format(Val([Cas]),"#-##-#")

Hello,
I have a lot of CAS#s, which come in two formats. One as nine digit
numbers, one with hyphens. I'd like to convert the nine digit numbers to
ones with hyphens. Hyphenated version would lob off the leading zeros and
add hyphens at the third and first from the right. Examples are as such:

000050000 to become 50-00-0
001234567 to become 1234-56-7
123456789 to become 123456-78-9
000123456 to become 123-45-6

How can I convert these numbers that are in a table?

Thanks for your help,
Ellen