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