Formatting Phone Number

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

Guest

Q: I have phone numbers in a text field with dashes (123-456-7890). I want to
remove the dashes so the phone number data looks like this (1234567890). What
update query will do this?
 
In the absence of any code, the text is stored exactly as it is typed. It
does sound like there is a Format property set on wherever you use to view
the data.
 
You could try using the Left, Mid and Right functions like this:

Expr1: Left([phoneno],3) & Mid([phoneno],5,3) & Mid([phoneno],10,4)

This is only one way to do it and it's not tested but I think it might work.
If the format of your phone number is not always 999-999-9999, then the SQL
won't work.
 
The data was imported from seversl text files, the text files included the
dashes and the phone number field was imported as text. I don't think there
is a format property set.
 
Try to use replace, you can create a query that displaty both fields

Select [Phone field name], Replace([Phone field name],"-","") as NewPhone
From TableName

You can also update that field based on the replace function
 
The replace query worked. Thank you,

Dave

Ofer said:
Try to use replace, you can create a query that displaty both fields

Select [Phone field name], Replace([Phone field name],"-","") as NewPhone
From TableName

You can also update that field based on the replace function
--
I hope that helped
Good luck


David Rivera said:
The data was imported from seversl text files, the text files included the
dashes and the phone number field was imported as text. I don't think there
is a format property set.
Click to expand...
Click to expand...
 
This is funny... I was just about to ask the opposite question..

My phone numbers are 9999999999 I want to ENTER dashes in the correct
spots. 999-999-9999

What query would I use to format my numbers like that?

Dale
 
Hi Dale,

To add the dashes:
phone = Format(phone, "000-000-0000")

To remove the dashes:
phone = Replace(phone,"-","")

(Note: all quote marks are double quotes)
 
Back
Top