Maybe something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
myRng.NumberFormat = "[<=9999999]###-####;(###) ###-####"

For Each myCell In myRng.Cells
With myCell
If IsEmpty(myCell.Value) Then
'do nothing
Else
.Value = 444444 & Right(.Text, 4)
End If
End With
Next myCell
End Sub

Hemming wrote:

I have a column of telephone numbers. I need to change the area code and
first three digits of the number to a new area code and new first three
digits. I need to do this for the entire column. example: 5555555555
change to (444)444-5555. the only part of the number that will stay the
same is the last 4 digits. if there are 100 different telephone numbers
there will be 100 unique last 4 digits. the formula
=value("444444"&right(a1:a100,4)) works well as a formula and changing
the cell format to special, telephone number , but I really wanted to
use in a marco. can anyone help .....thanks

--
Hemming
------------------------------------------------------------------------
Hemming's Profile: http://www.excelforum.com/member.php...o&userid=32185
View this thread: http://www.excelforum.com/showthread...hreadid=520555

--

Dave Peterson