Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting addresses from spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting addresses from spreadsheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting addresses from spreadsheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting addresses from spreadsheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to separate names and addresses in a spreadsheet | Excel Worksheet Functions | |||
Extracting email addresses from hyperlinks. | Excel Discussion (Misc queries) | |||
Email Addresses in a spreadsheet | Excel Worksheet Functions | |||
how to setup a spreadsheet with addresses | New Users to Excel | |||
Extracting email addresses from Outlook Contacts | Excel Programming |