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

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

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