Formatting Phone Numbers

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?

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

On Fri, 1 Jul 2005 14:14:16 -0500, Tha BeatMaker
<[email protected]> wrote:

>
>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?

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]...
>
> 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?
>
>
> --
> Tha BeatMaker
> ------------------------------------------------------------------------
> Tha BeatMaker's Profile:
http://www.excelforum.com/member.php...o&userid=23998
> View this thread: http://www.excelforum.com/showthread...hreadid=383980
>

Originally Posted by 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

[/color]

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.

See inline for error handling addition.

On Fri, 1 Jul 2005 17:52:18 -0500, Tha BeatMaker
<[email protected]> wrote:

>
>Gord Dibben Wrote:
>> 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"
>> End Sub


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.[/color]

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]...
>
> 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?
>
>
> --
> Tha BeatMaker
> ------------------------------------------------------------------------
> Tha BeatMaker's Profile:
http://www.excelforum.com/member.php...o&userid=23998
> View this thread: http://www.excelforum.com/showthread...hreadid=383980
>

[QUOTE=Gord Dibben]

>> 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[/color][/color]
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"
>> End Sub

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.

Originally Posted by Tha BeatMaker

>> 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[/color][/color]
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"
>> End Sub

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