extract a phone number from formatting
 

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?

extract a phone number from formatting
 

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

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

"Nick C" wrote:

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?

extract a phone number from formatting
 

Perfect! Thank you!!

"tim m" wrote:

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

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

"Nick C" wrote:

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?

extract a phone number from formatting
 

Couple of methods......

1. EditReplace 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

On Thu, 24 Aug 2006 10:20:01 -0700, Nick C <Nick
wrote:

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?