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

[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

 

[Jason Morin]

Try this, Fluffy:

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

HTH
Jason
Atlanta, GA

 

[Dave Peterson]

Just kind of an unfortuate line break:

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

 

[Guest]

Just kind of an unfortuate line break:

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

 

[Dave Peterson]

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

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

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

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

 

[Ron Rosenfeld]

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