Formatting Phone Numbers

[Tha BeatMaker]

Consider this example:

A1 -> 3365551111
A2 -> 336-555-2222
A3 -> (336) 555-3333


I want to be able to scan through column A and change all numbers to be
in the format of A1 which is just straight numbers...no punctuation.

How would I do that?

 

[Gord Dibben]

Easiest would be to use a macro.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
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

You could manually do an Edit>Replace of each unwanted character.


Gord Dibben Excel MVP

 

[RagDyer]

Depends on exactly what kind of data you have.

If they're true numbers, check and see if there may be an existing custom
format in force for those cells, where you can then just *reformat* them to
your desired configuration.

If they're text, then try this text formula in an adjoining "helper" column:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),") ",""),"(","")

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Tha BeatMaker" <[email protected]>
wrote in message
news:[email protected]...

 

[Tha BeatMaker]

Easiest would be to use a macro.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
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

[/QUOTE]


Can you add something to tell it to skip if all numbers are in the A1
format? I tried running this macro on a set of numbers that were all
in the correct format and it gave me a debug error.

 

[Gord Dibben]

See inline for error handling addition.

Easiest would be to use a macro.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String On Error GoTo Endit
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

Endit:
masgbox "Cells contain numbers only"

Gord

Can you add something to tell it to skip if all numbers are in the A1
format? I tried running this macro on a set of numbers that were all
in the correct format and it gave me a debug error.[/QUOTE]

 

[CLR]

This is a standard feature of the ASAP Utilities add-in, called "Advanced
character removal",
available free at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



"Tha BeatMaker" <[email protected]>
wrote in message
news:[email protected]...

 

[Tha BeatMaker]

On Error GoTo Endit
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

[/QUOTE]
Endit:
masgbox "Cells contain numbers only"
this macro works good but it's correctly being used in the middle o
another macro so everything stops. how can i change the error checke
to continue with the macro if the cells contain numbers only? I n
longer want the msgbox[/QUOTE]

 

[Tha BeatMaker]

On Error GoTo Endit
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

[/QUOTE]
Endit:
masgbox "Cells contain numbers only"[/QUOTE]

this macro works good but it's correctly being used in the middle of
another macro so everything stops. how can i change the error checker
to continue with the macro if the cells contain numbers only? I no
longer want the msgbox