I have a list of thousands of phone numbers. I have been trying
various formulas to no success. My phone numbers look like this:

(123)456-7890

and I need them to look like this:

1234567890

They are in columns. Thanks.

Do 3 replace, ctrl + h then in the find what box put (
leave the replace with empty

do the same with ) and -


--


Regards,


Peo Sjoblom


"biffula" wrote in message
oups.com...
I have a list of thousands of phone numbers. I have been trying
various formulas to no success. My phone numbers look like this:

(123)456-7890

and I need them to look like this:

1234567890

They are in columns. Thanks.

You could use Find/Replace for each of the 3 symbols to eliminate.

Select all of your phone numbers
From the Edit Menu, choose "Replace"
In the Find What field, enter: (
Leave the Replace With field blank
Click "Replace All"
Repeat for ) and - symbols

HTH,
Elkar


"biffula" wrote:

I have a list of thousands of phone numbers. I have been trying
various formulas to no success. My phone numbers look like this:

(123)456-7890

and I need them to look like this:

1234567890

They are in columns. Thanks.

On Nov 2, 1:22 pm, Elkar wrote:
You could use Find/Replace for each of the 3 symbols to eliminate.

Select all of your phone numbers
From the Edit Menu, choose "Replace"
In the Find What field, enter: (
Leave the Replace With field blank
Click "Replace All"
Repeat for ) and - symbols



So simple. It worked. Thanks everyone. NOW, my problem is some of
the numbers had spaces instead of hyphens. Any way to close the
numbers up?

Some now look like this:

123456 7890

Need to look like this:

1234567890

doing the same, replace space with nothing, hit space key once in the find
what box and leave replace with empty


--


Regards,


Peo Sjoblom


"biffula" wrote in message
ups.com...
On Nov 2, 1:22 pm, Elkar wrote:
You could use Find/Replace for each of the 3 symbols to eliminate.

Select all of your phone numbers
From the Edit Menu, choose "Replace"
In the Find What field, enter: (
Leave the Replace With field blank
Click "Replace All"
Repeat for ) and - symbols



So simple. It worked. Thanks everyone. NOW, my problem is some of
the numbers had spaces instead of hyphens. Any way to close the
numbers up?

Some now look like this:

123456 7890

Need to look like this:

1234567890

Do the same thing. Find Space and replace with blank.

HTH,
Elkar


"biffula" wrote:

On Nov 2, 1:22 pm, Elkar wrote:
You could use Find/Replace for each of the 3 symbols to eliminate.

Select all of your phone numbers
From the Edit Menu, choose "Replace"
In the Find What field, enter: (
Leave the Replace With field blank
Click "Replace All"
Repeat for ) and - symbols



So simple. It worked. Thanks everyone. NOW, my problem is some of
the numbers had spaces instead of hyphens. Any way to close the
numbers up?

Some now look like this:

123456 7890

Need to look like this:

1234567890

Here's what I do. Select the cells you want to change and run this
macro.

Sub Convert_Phone()
Application.ScreenUpdating = False

With Selection.SpecialCells(xlConstants)
.Replace what:=Chr(160), Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:=Chr(32), Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:=")", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:="(", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:="-", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:="+", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:=".", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
.Replace what:="'", Replacement:="", lookat:=xlPart,
searchorder:=xlByColumns, MatchCase:=True
End With

Application.ScreenUpdating = True
End Sub


HTH,
JP

On Nov 2, 2:07 pm, biffula wrote:
I have a list of thousands of phone numbers. I have been trying
various formulas to no success. My phone numbers look like this:

(123)456-7890

and I need them to look like this:

1234567890

They are in columns. Thanks.