Extracting addresses from spreadsheet

[Steve Wylie]

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

 

[Jim May]

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]

Well, I shall certainly make a note of your =RIGHT(A2,LEN
(A2)-FIND(CHAR(32),A2)) solution as that sounds very
useful for future reference when dealing with addresses
typed with the house number in. However, when I say the
addresses are typed in a sporadic manner, I'm afraid that
this means that not all the street details are in the
same column, so I still need a macro outlined in my
original post.

Thank you for your suggestion, though.

Steve

 

[Robin Clay]

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.

So... just record what you are doing, then have a look at
the code that is generated, and modify to suit.

Start by pressing Tools > Macro > Record New Macro
do a couple of typical cycles, and then stop the
recording Tools > Macro > Stop recording

Press Alt F11 and go to the appropriate Module.


RClay AT haswell DOT com