Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do insert page numbers in excel | Excel Discussion (Misc queries) | |||
Excel: Which numbers in a column equal a certain number (withou. | Excel Worksheet Functions | |||
print tickets with incrementing numbers in Excel ? | Excel Worksheet Functions | |||
How do I get Excel to correctly add a column of numbers that have. | Excel Discussion (Misc queries) | |||
How do i increase the size of numbers in a text in Excel. | Excel Worksheet Functions |