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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Just kind of an unfortuate line break:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
 
Dave Peterson said:
Just kind of an unfortuate line break:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
Click to expand...
 
Jason's formula returns a Text value in that format.

I'm not sure what you mean about the numberformat stuff.
 
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
Click to expand...


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
 
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
Click to expand...

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
 
Back
Top