change format for phone numbers automatically

select the column with yoiur telephone numbers
use edit find replace and enter - for find and nothing for replace
replace all (Note depending on the version of Excel you have, You might
have to do this slightly differently) make sure you try it on a copy before
you do it on th emaster file.
do also find ( replace nothing
do also Find ) replace nothing

"vms" wrote:

> I have a worksheet with phone numbers in a column. Some are set up (xxx)
> xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
> Is there a way to do this without retyping all the wrong ones or deleting
> each space or -?
> --
> "You can have everything in life you want, if you will just help enough
> other people get what they want." -- Zig Ziglar
>

vms

Manually......edit>replace

what: (
with: nothing

replace all

Do this for each character (,),-

Alternative..........Run this macro on your selected range.........

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


Gord Dibben Excel MVP


On Tue, 14 Jun 2005 12:21:02 -0700, vms <[email protected]> wrote:

>I have a worksheet with phone numbers in a column. Some are set up (xxx)
>xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
>Is there a way to do this without retyping all the wrong ones or deleting
>each space or -?

I have a worksheet with phone numbers in a column. Some are set up (xxx)
xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
Is there a way to do this without retyping all the wrong ones or deleting
each space or -?
--
"You can have everything in life you want, if you will just help enough
other people get what they want." -- Zig Ziglar

select the column with yoiur telephone numbers
use edit find replace and enter - for find and nothing for replace
replace all (Note depending on the version of Excel you have, You might
have to do this slightly differently) make sure you try it on a copy before
you do it on th emaster file.
do also find ( replace nothing
do also Find ) replace nothing

"vms" wrote:

> I have a worksheet with phone numbers in a column. Some are set up (xxx)
> xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
> Is there a way to do this without retyping all the wrong ones or deleting
> each space or -?
> --
> "You can have everything in life you want, if you will just help enough
> other people get what they want." -- Zig Ziglar
>

vms

Manually......edit>replace

what: (
with: nothing

replace all

Do this for each character (,),-

Alternative..........Run this macro on your selected range.........

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


Gord Dibben Excel MVP


On Tue, 14 Jun 2005 12:21:02 -0700, vms <[email protected]> wrote:

>I have a worksheet with phone numbers in a column. Some are set up (xxx)
>xxx-xxxx some are xxx-xxx-xxxx they all need to be xxxxxxxxxx
>Is there a way to do this without retyping all the wrong ones or deleting
>each space or -?