Phone Number Excel 2003

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 :)!
 
D

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???)
 
I

Icehearted

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

Thanks :)
 
G

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
 
D

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.
 
S

Simon Lloyd

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

Code
-------------------
Dim Arr, i As Lon
Arr = Array(".", "/", ")", "(", " ", "'", ",", "-", Chr(34)
For i = 0 To 8 '<---change to reflect number of elements in array beginning at
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:=Fals
Next
-------------------

Icehearted;507572 said:
Hi

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

Thanks much :)

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
S

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.
 
I

Icehearted

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)
 
I

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top