Formatting Phone Number

D

David Rivera

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?
 
J

JohnFol

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

Mo

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

David Rivera

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

Ofer

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
 
D

David Rivera

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

Dale

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
 
S

SusanV

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top