Hi
try the following macro
Sub change_rows()
Dim RowNdx As Long
Dim LastRow As Long
Dim pos as integer

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
pos = InStrRev(.value," ")
if pos0 then
.offset(0,1).value=mid(.value,pos+1,10)
.value=left(.value,pos-1)
end if
End with
Next RowNdx
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"Brian" schrieb im Newsbeitrag
...
yes, it's the last 'word' of the address and it has a
space in front of it: 123 MAIN ST for example.
-----Original Message-----
Hi
is this prefix somehow separated from the rest of the
text (e.g. with a
space)?

--
Regards
Frank Kabel
Frankfurt, Germany

"Brian" schrieb im
Newsbeitrag
...
I have a list of addresses and need to move the suffix
(DR, ST, AVE, CIR, RD) to the cell to the right. Is
there
a macro I could program to do this? I have alot of them
and don't really want to do this by hand one at a time.

Thanks.

.