In Excel, I want to have all telephone numbers display in the sam.

Hi All,
I have a listing of names, addresses, etc. in an Excel document. The
telephone numbers have been entered in several different formats:
123/456-1234, (123)345-1234, 123-455-1234. Is there a way to format the
column so that these numbers will all be displayed in the same format? If
so, please explain how. Thanks,
Fluffy from Wisconsin
Reading Teacher

Try this, Fluffy:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
","")),"(000) 000-0000")

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi All,
> I have a listing of names, addresses, etc. in an Excel
document. The
>telephone numbers have been entered in several different
formats:
>123/456-1234, (123)345-1234, 123-455-1234. Is there a
way to format the
>column so that these numbers will all be displayed in
the same format? If
>so, please explain how. Thanks,
>Fluffy from Wisconsin
>Reading Teacher
>.
>

Just kind of an unfortuate line break:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")

Jason Morin wrote:
>
> Try this, Fluffy:
>
> =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
> (SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
> ","")),"(000) 000-0000")
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Hi All,
> > I have a listing of names, addresses, etc. in an Excel
> document. The
> >telephone numbers have been entered in several different
> formats:
> >123/456-1234, (123)345-1234, 123-455-1234. Is there a
> way to format the
> >column so that these numbers will all be displayed in
> the same format? If
> >so, please explain how. Thanks,
> >Fluffy from Wisconsin
> >Reading Teacher
> >.
> >

--

Dave Peterson

"Dave Peterson" wrote:

> Just kind of an unfortuate line break:
>
> =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
> SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
>
> Jason Morin wrote:
> >
> > Try this, Fluffy:
> >
> > =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
> > (SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
> > ","")),"(000) 000-0000")
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >Hi All,
> > > I have a listing of names, addresses, etc. in an Excel
> > document. The
> > >telephone numbers have been entered in several different
> > formats:
> > >123/456-1234, (123)345-1234, 123-455-1234. Is there a
> > way to format the
> > >column so that these numbers will all be displayed in
> > the same format? If
> > >so, please explain how. Thanks,
> > >Fluffy from Wisconsin
> > >Reading Teacher
> > >.
> > >
>
> --
>
> Dave Peterson
> I tried the format you gave me, but all I get is "Microsoft Office Excel cannot use the number format you typed." When I click on the "help" for more informaiton about custom number formats, I just get a blank box. Any other ideas? Thanks again, Judy

Jason's formula returns a Text value in that format.

I'm not sure what you mean about the numberformat stuff.

Fluffy from Wisconsin wrote:
>
> "Dave Peterson" wrote:
>
> > Just kind of an unfortuate line break:
> >
> > =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
> > SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
> >
> > Jason Morin wrote:
> > >
> > > Try this, Fluffy:
> > >
> > > =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
> > > (SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
> > > ","")),"(000) 000-0000")
> > >
> > > HTH
> > > Jason
> > > Atlanta, GA
> > >
> > > >-----Original Message-----
> > > >Hi All,
> > > > I have a listing of names, addresses, etc. in an Excel
> > > document. The
> > > >telephone numbers have been entered in several different
> > > formats:
> > > >123/456-1234, (123)345-1234, 123-455-1234. Is there a
> > > way to format the
> > > >column so that these numbers will all be displayed in
> > > the same format? If
> > > >so, please explain how. Thanks,
> > > >Fluffy from Wisconsin
> > > >Reading Teacher
> > > >.
> > > >
> >
> > --
> >
> > Dave Peterson
> > I tried the format you gave me, but all I get is "Microsoft Office Excel cannot use the number format you typed." When I click on the "help" for more informaiton about custom number formats, I just get a blank box. Any other ideas? Thanks again, Judy

--

Dave Peterson

On Thu, 17 Mar 2005 09:25:07 -0800, Fluffy from Wisconsin
<FluffyfromWisconsin@discussions.microsoft.com> wrote:

>Hi All,
> I have a listing of names, addresses, etc. in an Excel document. The
>telephone numbers have been entered in several different formats:
>123/456-1234, (123)345-1234, 123-455-1234. Is there a way to format the
>column so that these numbers will all be displayed in the same format? If
>so, please explain how. Thanks,
>Fluffy from Wisconsin
>Reading Teacher


In addition to the formula approach, you could also use a VBA macro.

To enter this macro, <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use the macro, select a range of "phone-number" cells. Then <alt-F8> opens
the macro dialog box. Select FixPhoneNums and <Run>.


--ron

Fluffy

You could also use a macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub

Select the range of cells then run the macro.

Then Format as Special>Phone Number


Gord Dibben Excel MVP

On Thu, 17 Mar 2005 16:01:02 -0800, Fluffy from Wisconsin
<FluffyfromWisconsin@discussions.microsoft.com> wrote:

>
>
>"Dave Peterson" wrote:
>
>> Just kind of an unfortuate line break:
>>
>> =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
>> SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
>>
>> Jason Morin wrote:
>> >
>> > Try this, Fluffy:
>> >
>> > =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
>> > (SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
>> > ","")),"(000) 000-0000")
>> >
>> > HTH
>> > Jason
>> > Atlanta, GA
>> >
>> > >-----Original Message-----
>> > >Hi All,
>> > > I have a listing of names, addresses, etc. in an Excel
>> > document. The
>> > >telephone numbers have been entered in several different
>> > formats:
>> > >123/456-1234, (123)345-1234, 123-455-1234. Is there a
>> > way to format the
>> > >column so that these numbers will all be displayed in
>> > the same format? If
>> > >so, please explain how. Thanks,
>> > >Fluffy from Wisconsin
>> > >Reading Teacher
>> > >.
>> > >
>>
>> --
>>
>> Dave Peterson
>> I tried the format you gave me, but all I get is "Microsoft Office Excel cannot use the number format you typed." When I click on the "help" for more informaiton about custom number formats, I just get a blank box. Any other ideas? Thanks again, Judy

On Thu, 17 Mar 2005 20:10:01 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Thu, 17 Mar 2005 09:25:07 -0800, Fluffy from Wisconsin
><FluffyfromWisconsin@discussions.microsoft.com> wrote:
>
>>Hi All,
>> I have a listing of names, addresses, etc. in an Excel document. The
>>telephone numbers have been entered in several different formats:
>>123/456-1234, (123)345-1234, 123-455-1234. Is there a way to format the
>>column so that these numbers will all be displayed in the same format? If
>>so, please explain how. Thanks,
>>Fluffy from Wisconsin
>>Reading Teacher
>
>
>In addition to the formula approach, you could also use a VBA macro.
>
>To enter this macro, <alt-F11> opens the VB Editor.
>
>Ensure your project is highlighted in the Project Explorer window, then
>Insert/Module and paste the code below into the window that opens.
>
>To use the macro, select a range of "phone-number" cells. Then <alt-F8> opens
>the macro dialog box. Select FixPhoneNums and <Run>.
>
>
>--ron

I suppose it would be more useful if I posted the macro:

======================================
Sub FixPhoneNums()
Dim c As Range
Dim i As Long
Dim s As Variant, temp As Variant

For Each c In Selection
For i = 1 To Len(c.Text)
s = Mid(c.Text, i, 1)
If IsNumeric(s) Then temp = temp & s
Next i

With c
.Value = temp
.NumberFormat = "[<=9999999]###-####;(###) ###-####"
End With

temp = ""
Next c
End Sub
=============================


--ron