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

[Bayou Johnny]

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?

 

[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

 

[Michael Bowers]

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