extract a phone number from formatting

[Guest]

Hello,
I am trying to extract a long list of phone numbers from text
i.e.:
i am trying to get 9998887777 out of (999)888-7777
(these cells are not formatted, the parenthesis and dashes were manually
typed in)
Any good ideas?

 

[Guest]

=MID(A1,2,3)&MID(A1,6,3)&RIGHT(A1,4)

This should do the trick (if your data is in cell A1)

 

[Guest]

Perfect! Thank you!!

=MID(A1,2,3)&MID(A1,6,3)&RIGHT(A1,4)

This should do the trick (if your data is in cell A1)

 

[Gord Dibben]

Couple of methods......

1. Edit>Replace 3 times to remove the () and - with nothing

2. A macro.

Sub RemoveAlphas()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP