How to Convert data to Phone Number format?

[SinCity]

I have a table that contains some data like this...

Phone_Numbers
212-560-5602
212-587-2938

And I have an Excel document that contains data like this...

phone_numbers
(211) 787-3957
(217) 827-2999

How can I import the Excel data into the Access Database so that they are
treated
both as phone numbers? I also do not want to allow duplicates.

Please help!

 

[Bill Mosca, MS Access MVP]

Run an update like this:

Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")

The inside Replace is removing the left paren. The outside Replace removes
the right paren and space.

You might want to store just the numbers and use formatting and input masks
for the user. That way you stay flexible as to how th enumber displays.

 

[SinCity]

Bill,

Forgive me (I'm a newbie).....but how exactly do I run the following command
that you suggested?


Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")


If you wouldn't mind telling me what to point and click on I would
appreciate it.
(Access 2003)

Thx,
Mark

Run an update like this:

Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")

The inside Replace is removing the left paren. The outside Replace removes
the right paren and space.

You might want to store just the numbers and use formatting and input
masks for the user. That way you stay flexible as to how th enumber
displays.

--
Bill Mosca, MS Access MVP

I have a table that contains some data like this...

Phone_Numbers
212-560-5602
212-587-2938

And I have an Excel document that contains data like this...

phone_numbers
(211) 787-3957
(217) 827-2999

How can I import the Excel data into the Access Database so that they are
treated
both as phone numbers? I also do not want to allow duplicates.

Please help!

 

[Bill Mosca, MS Access MVP]

No problem, Mark.

Open a new query in design view. When the box pops up to pick a table, close
it without picking anything. Click on View>SQL view.

Paste what I gave you into the blank window.
Change MyTable to the name of your table.
Change PhoneNum to the name of your phone field.
Click the ! icon to run the query.

--
Bill Mosca, MS Access MVP

Bill,

Forgive me (I'm a newbie).....but how exactly do I run the following
command that you suggested?


Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")


If you wouldn't mind telling me what to point and click on I would
appreciate it.
(Access 2003)

Thx,
Mark

Run an update like this:

Update MyTable
Set [PhoneNum] = Replace(Replace([PhoneNum],"(",""),") ","-")

The inside Replace is removing the left paren. The outside Replace
removes the right paren and space.

You might want to store just the numbers and use formatting and input
masks for the user. That way you stay flexible as to how th enumber
displays.

--
Bill Mosca, MS Access MVP

I have a table that contains some data like this...

Phone_Numbers
212-560-5602
212-587-2938

And I have an Excel document that contains data like this...

phone_numbers
(211) 787-3957
(217) 827-2999

How can I import the Excel data into the Access Database so that they
are treated
both as phone numbers? I also do not want to allow duplicates.

Please help!

 

[SinCity]

Hi Bill,

Thanks again for your past post. It worked beautifully. I am making some
other changes to my database that are similar to the one that you helped me
with. Would you mind helping me understand the following statement?

Set [PhoneNum] = Replace(Replace([PhoneNum], "(" , "" )," ) "
,"-")

Your syntax does seem to work, but it also seems a little different then the
syntax I found on the web.

Thanks much!

 

[Bill Mosca, MS Access MVP]

We're using a replace function inside of another replace function. I did
that because we have 2 pieces of the original string that need work. The
open parenthesis must be removed and the closed paren and one space must be
replaced with a dash.

When you nest functions, the inside one is run first, then the outside one.
There is no real order necessary here. Either function could have been on
the inside. If I used one function at a time, I would need to run 2 separate
queries.

Replace is a built-in function with 3 parameters.
1. The string you want to change.
2. The piece you want changed.
3. The piece you want the change to be.

In the inside Replace function:
Replace([PhoneNum], "(" , "" )

[PhoneNum] is the string.
( is the character to search for.
An empty string (2 double quotes with nothing in the middle) is what we want
put in. In other words, we want to replace an open parenthesis with nothing.
So "(555) 555-1235" becomes "555) 555-1234"

Once the open paren is removed, we can replace the close paren and one space
with a dash. Now "555) 555-1234" becomes 555-555-1234.

That help?

 

[SinCity]

That help?

Thanks Bill. Your explanation was awesome! Yes that helped.