Phone Number Excel 2003

  • Thread starter Thread starter Icehearted
  • Start date Start date
I

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 :)!
 
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???)
 
is there a way to automate this process so i dont have to go through 1 by one?

Thanks :)
 
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
 
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.
 
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.
 
Thank you very much.

Simon Lloyd said:
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)
Click to expand...
 
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 :)
 
Back
Top