Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

I would select column G and do a bunch of edit|replaces.

Get rid of any special character (like hyphens, open/close parens, dots, spaces)
so that you're left with nothing but the digits.

Then apply the format you want
Format|cells|number tab|special Phone number (maybe???)

Icehearted wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

--

Dave Peterson

is there a way to automate this process so i dont have to go through 1 by one?

Thanks :)

"Dave Peterson" wrote:

I would select column G and do a bunch of edit|replaces.

Get rid of any special character (like hyphens, open/close parens, dots, spaces)
so that you're left with nothing but the digits.

Then apply the format you want
Format|cells|number tab|special Phone number (maybe???)

Icehearted wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

--

Dave Peterson

Easy way is to copy this UDF to a general module in your workbook.

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

Then in a helper column enter =remalpha(G1)*1

Copy down to remove all but numbers.

Copy the list and EditPaste SpecialValyesOKEsc

Format those numbers as you wish.


Gord Dibben MS Excel MVP

On Tue, 29 Sep 2009 15:21:02 -0700, Icehearted
wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

If you want it automated, you could use Gord's UDF.

Personally, I'd just look at the data to see the characters to remove. The
edit|replaces would go pretty fast.

Icehearted wrote:

is there a way to automate this process so i dont have to go through 1 by one?

Thanks :)

"Dave Peterson" wrote:

I would select column G and do a bunch of edit|replaces.

Get rid of any special character (like hyphens, open/close parens, dots, spaces)
so that you're left with nothing but the digits.

Then apply the format you want
Format|cells|number tab|special Phone number (maybe???)

Icehearted wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

--

Dave Peterson


--

Dave Peterson

To be sure, Dave is suggessting use find/Replace, on the enitre set of data
at once..

Find ( Replace with (leave blank) Replace All.

Only takes as long as the number of different symbols/characters/spaces you
have.

"Icehearted" wrote:

is there a way to automate this process so i dont have to go through 1 by one?

Thanks :)

"Dave Peterson" wrote:

I would select column G and do a bunch of edit|replaces.

Get rid of any special character (like hyphens, open/close parens, dots, spaces)
so that you're left with nothing but the digits.

Then apply the format you want
Format|cells|number tab|special Phone number (maybe???)

Icehearted wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

--

Dave Peterson

Thank you this worked....my boss told me to change it all to text since he's
going to export it to Access.

/GAH!
//PULLS HAIR OUT

Thanks all :)

"Sean Timmons" wrote:

To be sure, Dave is suggessting use find/Replace, on the enitre set of data
at once..

Find ( Replace with (leave blank) Replace All.

Only takes as long as the number of different symbols/characters/spaces you
have.

"Icehearted" wrote:

is there a way to automate this process so i dont have to go through 1 by one?

Thanks :)

"Dave Peterson" wrote:

I would select column G and do a bunch of edit|replaces.

Get rid of any special character (like hyphens, open/close parens, dots, spaces)
so that you're left with nothing but the digits.

Then apply the format you want
Format|cells|number tab|special Phone number (maybe???)

Icehearted wrote:

Hi -

Was wondering if theres a way to make all the phone numbers in my
spreadsheet uniform. I have a list of phone numbers that were copied and
pasted into a workbook. Some of these phone numbers have parenthesis, some
have spaces, some have dashes. The phone numbers are all in Column G.

Thanks much :)!

--

Dave Peterson