Paul,

Try this in your module...

Option Explicit
Dim cell As Range
Sub stripzip()
For Each cell In Range("A:A")
If Len(cell) = 9 And IsNumeric(cell) Then
Cells(cell.Row, 2) = Left(cell.Value, 5) 'Could Delete
Cells(cell.Row, 3) = "-" 'Could Delete
Cells(cell.Row, 4) = Right(cell.Value, 4) 'Could Delete
Cells(cell.Row, 5) = Left(cell.Value, 5) & "-" & Right(cell.Value, 4)
End If
Next cell
End Sub

This will do exactly as you specified. IMHO you can cut out the
intermediate steps (Marked with 'Could Delete) and save some run time.

Mike

"Paulg" wrote:


I have a macro that takes a 9 digit number from one cell and converts it
into a zipcode + 4 number.

If the number is in A1 I set focus on B1 and =left(a1,5), then in C1 I
put the dash, (-) in d1 =right(A1,4) then I concatentate the 3 cells in
E1, and then autofill the columns down to the last row with the 9 digit
number.

My problem is that the incoming sheets can have rows fro 100 to 10,000
so in the Macro I have to set the autofill command to 10,005 to make
sure I fill the sheet far enough. Of course, when I have a sheet of
100, I have to go back and delete all the dashes that filled to row
10,005.

Is there a way to autofill the inserted columns to the same row as the
original column with the 9 digits in it???

Thanks

Paul


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=561623