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