Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to change Phone Number format | Excel Discussion (Misc queries) | |||
Phone Number Excel 2003 | Excel Discussion (Misc queries) | |||
How do I delete dashes from a phone number xxx-xxx-xxxx in Excel? | Excel Worksheet Functions | |||
Excel VB form to break apart a phone number? | Excel Programming | |||
How to format a phone number in Excel | Excel Discussion (Misc queries) |