|
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(SUBSTI TUTE (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(SUBSTI TUTE( SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000") Jason Morin wrote: Try this, Fluffy: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE (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(SUBSTI TUTE( SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000") Jason Morin wrote: Try this, Fluffy: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE (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(SUBSTI TUTE( SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000") Jason Morin wrote: Try this, Fluffy: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE (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
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 SpecialPhone Number Gord Dibben Excel MVP On Thu, 17 Mar 2005 16:01:02 -0800, Fluffy from Wisconsin wrote: "Dave Peterson" wrote: Just kind of an unfortuate line break: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE( SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000") Jason Morin wrote: Try this, Fluffy: =TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE (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
wrote: On Thu, 17 Mar 2005 09:25:07 -0800, Fluffy from Wisconsin 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 |
| All times are GMT +1. The time now is 05:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com