Common phone number format

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

Guest

I have a database of approximately 4000 addresses.

I'd like to uniformly format 3 different telephone number fields. There are
currently 3 different formats in the database.

I would like the format to be (111) 222-3333

the three formats contained are 111-222-3333 , 1112223333, and (111) 222-3333

I can modify the second format 1112223333 using right() and left()
functions. Is there a way to remove the non numerical characters from the
strings?

Thanks in advance

Pip'n
 
Test this on a copy of your data

UPDATE PhoneTable
SET PhoneNumber = Format(Replace(Replace([PhoneNumber],"-",""),"
",""),"(@@@) @@@-@@@@")
WHERE PhoneNumber Not Like "(*"

Another way is to use a function that strips all non-number characters out
and then applies the format
 
I have a database of approximately 4000 addresses.

I'd like to uniformly format 3 different telephone number fields. There are
currently 3 different formats in the database.

I would like the format to be (111) 222-3333

the three formats contained are 111-222-3333 , 1112223333, and (111) 222-3333

I can modify the second format 1112223333 using right() and left()
functions. Is there a way to remove the non numerical characters from the
strings?

Thanks in advance

Pip'n
Click to expand...

NewPhoneFormat:
Format(Replace(Replace(Replace(Replace([PhoneField],"(",""),")",""),"-",""),"
",""),"(@@@) @@@-@@@")

That last replace argument value may not appear properly on your page
because of email wrap.
It's ..... ," ",""),"(@@@) etc. (the space between the quotes.)
 
That worked great. Thank you

John Spencer said:
Test this on a copy of your data

UPDATE PhoneTable
SET PhoneNumber = Format(Replace(Replace([PhoneNumber],"-",""),"
",""),"(@@@) @@@-@@@@")
WHERE PhoneNumber Not Like "(*"

Another way is to use a function that strips all non-number characters out
and then applies the format

Pip''''n said:
I have a database of approximately 4000 addresses.

I'd like to uniformly format 3 different telephone number fields. There
are
currently 3 different formats in the database.

I would like the format to be (111) 222-3333

the three formats contained are 111-222-3333 , 1112223333, and (111)
222-3333

I can modify the second format 1112223333 using right() and left()
functions. Is there a way to remove the non numerical characters from the
strings?

Thanks in advance

Pip'n
Click to expand...
Click to expand...
 
Back
Top