How To Separate Addresses In Excel From One Column To Four

For my video course with tips on Excel productivity go here [ Ссылка ]

Here's what I've done step by step:
1. I inserted a formula =RIGHTM2,9 into the first column to the right of your addresses assuming they are in column M, starting with row 2. That read state and zip code. I copied column N were now the formula resides and pasted it special-values into that same column to wipe out formulas so I have only values.
2. I selected all rows with data on the sheet and sorted it by column N where we now have state and zip, so I can easily see where zip is 9 digits rather than 5 and just typed TN where it was missing - about 5-10 instances on each sheet. Then I selected column N with state and zip again, did H+Ctrl to open 'Find and Replace' option, typed " TN" without quotation marks in the Find box, typed "TN" and clicked 'Replace all' - this removed space before TN in all records on the spreadsheet. Then I selected that column and did Data-Text to columns to separate zip from state.
3. I selected column M containing your addresses, did H+Ctrl to open 'Find and Replace' option, typed "TN*" without quotation marks in the Find box and clicked 'Replace all' - this removed state and zip from your addresses.
4. Next I selected all rows in your database again and sorted by column M to ensure that the list starts with data in column M, and not a blank cell. Then I inserted a column before column N, and started typing names of cities in it. I work in Excel 2013, it 'understands' what I am doing and offers me auto fill for the column to follow the pattern - in our case it is the last word in the record. Some cities have two word names, so they did not get transferred correctly- for instance Mt Juliet. To correct that I selected the column with city names, did H+Ctrl to open 'Find and Replace' option, typed "Juliet" without quotation marks in the Find box, typed "Mt Juliet" in Replace with box, and clicked 'Replace all' - this corrected the mistake for those cases but there were some records with Mount Juliet as city name, I did those separately after I've Sierra database again by column with cities - to make all records needed that correction to be displayed together makes the work easier too.
5. Next I selected column M containing your addresses, did H+Ctrl to open 'Find and Replace' option, typed "Mt Juliet" without quotation marks in the Find box and emptied Replace with box, and clicked 'Replace all' - this removed Mt Juliet from column M. I repeated that for all cities - easy to do as their manes are all together in the next column.
6. Some street addresses had city names in them, so I eye-balled all records in column M from top to bottom to find those - there were about 5-10 on each sheet.

Now the work was finished. If you think you can do this yourself next time, you can certainly do so, otherwise I will gladly do it for you again before your next mailing.

All the best!

Larissa


Here are more useful videos in my channel
http:youtu.be9u09QVbVRuc
http:youtu.beZlPRFsQbNig
http:youtu.beKYOmtd_Jvis
http:youtu.besnvPbFo1snU
http:youtu.beyWXoOFp8RJY
http:youtu.beEN6FojfJ20c
http:youtu.be8Hxpb9Um7ro
http:youtu.bexUDUByA1pxw
http:youtu.be9qVRxDXVLy8
http:youtu.beUYVksaUvgA4
http:youtu.beXurlWclYStM
http:youtu.beZVGP9_uqsns
http:youtu.beqU7uASqclvg
http:youtu.beUYwjcb9McJs
http:youtu.be0zbLREyuM8c
https:youtu.beo4OvpV2eu8Q

свернуть