Common phone number format

[Pip''''n]

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

 

[John Spencer]

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

 

[fredg]

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

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

 

[Pip''''n]

That worked great. Thank you

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