Add hyphens

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks, John!!!!

Ellen

John Spencer (MVP) said:
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
Click to expand...
Click to expand...
 
Back
Top