Excel phone number formula
 

Hello:

Let's say you have a phone number in a cell with the following format:
(555) 555-1212 Ext. 0000

How do you make that to be 55555512120000?

Thanks!

childofthe1980s

Excel phone number formula
 

Here's a formula that relies on exact the positioning of the digits:
=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)&RIGHT(A1,4)

and here's a macro that is a little more robust:
Sub StripNum()
Dim str As String
Dim cnt As Long
On Error Resume Next
For cnt = 1 To Len(ActiveCell.Value)
str = str & CDbl(Mid(ActiveCell.Value, cnt, 1))
Next
On Error GoTo 0
Debug.Print CDbl(str)
End Sub

Let me know if you need help applying either one
--
Charles Chickering

"A good example is twice the value of good advice."


"childofthe1980s" wrote:

Hello:

Let's say you have a phone number in a cell with the following format:
(555) 555-1212 Ext. 0000

How do you make that to be 55555512120000?

Thanks!

childofthe1980s

Excel phone number formula
 

Thanks, Charles! That worked perfectly!

childofthe1980s

"Charles Chickering" wrote:

Here's a formula that relies on exact the positioning of the digits:
=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)&RIGHT(A1,4)

and here's a macro that is a little more robust:
Sub StripNum()
Dim str As String
Dim cnt As Long
On Error Resume Next
For cnt = 1 To Len(ActiveCell.Value)
str = str & CDbl(Mid(ActiveCell.Value, cnt, 1))
Next
On Error GoTo 0
Debug.Print CDbl(str)
End Sub

Let me know if you need help applying either one
--
Charles Chickering

"A good example is twice the value of good advice."


"childofthe1980s" wrote:

Hello:

Let's say you have a phone number in a cell with the following format:
(555) 555-1212 Ext. 0000

How do you make that to be 55555512120000?

Thanks!

childofthe1980s

Excel phone number formula
 

I have been using these two functions for years to clean up data:

Function StripNumeric(Text As String) As String
' Removes all Numbers from a text string
' and returns the remaining text characters
Dim sTemp As String, i As Integer
sTemp = Text
For i = 0 To 9
sTemp = Application.Substitute(sTemp, i, "")
Next i
RemoveNumeric = sTemp
End Function

THis is the one you need

Function KeepNumeric(Text As String) As String
' Removes all text characters from a text string
' and returns the remaining numbers as a text 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


Thanks,
Mike


"Charles Chickering" wrote:

Here's a formula that relies on exact the positioning of the digits:
=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)&RIGHT(A1,4)

and here's a macro that is a little more robust:
Sub StripNum()
Dim str As String
Dim cnt As Long
On Error Resume Next
For cnt = 1 To Len(ActiveCell.Value)
str = str & CDbl(Mid(ActiveCell.Value, cnt, 1))
Next
On Error GoTo 0
Debug.Print CDbl(str)
End Sub

Let me know if you need help applying either one
--
Charles Chickering

"A good example is twice the value of good advice."


"childofthe1980s" wrote:

Hello:

Let's say you have a phone number in a cell with the following format:
(555) 555-1212 Ext. 0000

How do you make that to be 55555512120000?

Thanks!

childofthe1980s