With your current addresses starting at A2 enter in B2:
=RIGHT(A2,LEN(A2)-FIND(CHAR(32),A2)) Copy down - also copy and
paste-special as Value to make changes permanent.
This will display the full address stripping off the first several
characters before and including the first space character.
HTH

"Steve Wylie" wrote in message
...
I've got a spreadsheet which is used as a Word merge data
source. It's just names and addresses. However they are
sorted in name order and what is needed is a separate
Excel file for each street name. Okay, so just sort the
records in street order and cut & paste them to new
files. However, the addresses on the sheet have been
typed sporadically with addresses like "48 Acacia Avenue"
so I cannot sort a column on the street name. My only
option has been to do a Find on the sheet for each road
name (eg 'High Street') then select the entire row for
that record, cut, switch to the other sheet and paste it
in.

I could really use a macro that would do this
automatically. I would need to type in the road name,
then the macro searches for the first occurence, selects
that whole row, cuts, switches to the other sheet
(different Excel file, but could be other sheet in same
file if easier), paste row, move down one row, switch
back to master sheet, find next occurrence....

Would anyone here be able to write this macro, as my
Excel VBA is not as good as my Word VBA and I don't know
where to start!

Many thanks to anyone who can help!

Steve Wylie