How do I changet a text telephone number (xxx) xxx-xxxx to xxxxxx.

G

Guest

I have a data export that lists telephone number in a (xxx) xxx-xxxx format
that I need to change to a number format without spaces or commas. Anyone
know of a formula to apply to a column of text numbers to make such a
conversion within a spreadsheet?
 
D

Don Guillett

You could use a nested SUBSTITUTE formula or use edit/replace, or a macro
to do it for you.

Sub fixphonenumbers()
With Range("e1:e10")
.Replace "(", ""
.Replace ")", ""
.Replace " ", ""
.Replace "-", ""
End With
End Sub
 
G

Guest

Try this formula:

=VALUE(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ",""))))

or...

Save this function to your PERSONAL.xls file in a module:

Function KeepNumeric(Text As String) As String
Dim sTemp As String, i As Integer
sTemp = Text
For i = 1 To 255
If i < 48 Or i > 57 Then
sTemp = Application.Substitute(sTemp, Chr(i), "")
End If
Next i
KeepNumeric = sTemp
End Function

Then enter the formula: =Value(KeepNumeric(A1))

That should do it!

Thanks,
Mike
 

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

Similar Threads

How do I delete dashes from a phone number xxx-xxx-xxxx in Excel? 3
How do I change phone numbers from "xxxxxxxxxx" to "xxx-xxx-xxxx 1
How can I automatically add an area code to a number in a cell? 8
Changing Telephone Number Format from (xxx) xxx-xxxx to xxx-xxx-xx 1
Format Telephone Numbers 8
Here we are with the phone numbers again. I need to convert 7
Change existing phone numbers from xxx-xxx-xxxx to (xxx) xxx-xxxx format? 4
Time punch data from rows to columns 3

Top