Phone Number Excel 2003

[Icehearted]

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]

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]

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

Thanks

 

[Gord Dibben]

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 Edit>Paste Special>Valyes>OK>Esc

Format those numbers as you wish.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

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.

 

[Sean Timmons]

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]

Thank you very much.

Here's a little VBA code to do what you want:

Code:
--------------------
Dim Arr, i As Long
Arr = Array(".", "/", ")", "(", " ", "'", ",", "-", Chr(34))
For i = 0 To 8 '<---change to reflect number of elements in array beginning at 0
ActiveSheet.Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).Replace What:=Arr(i), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
--------------------





--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Icehearted]

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