Excel phone number formula

C

childofthe1980s

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
 
C

Charles Chickering

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
 
M

Michael Bowers

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
 

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

Top