Macro to clean and reformat address information

I'm trying to develop a macro that will be able to handle contact/address information in a variety of formats, to produce a cleaned, standardized list of said information. To be specific, I have spreadsheets that contain fields for first name, last name, address 1, address 2 (e.g., apt #, lot#), city, state, and ZIP code. I'd like to create a macro that will:

Assuming I start with a worksheet called "Original" that contains hundreds of cases and a separate, blank worksheet called "Cleaned":

1. Within worksheet "Original, apply "PROPER" capitalization rules to first name (column A), last name (column B), address 1 (column C), address 2 (column D), and city (column E)
2. Within worksheet "Original, apply "UPPER" capitalization rules to state (column F)
3. Within worksheet "Original, make sure that ZIP codes (column G) are in text format and that they include 5 digits (format "00000")
4. Concatenate first and last name into column A of the "Cleaned" worksheet
5. Add address 1 to column B in the "Cleaned" worksheet
6. Add address 2 to column C in the "Cleaned" worksheet
6. Concatenate city and state in column D of the "Cleaned" worksheet
7. Move ZIP code from original worksheet to column E of the "Cleaned" worksheet

Hope this makes sense.

Please post a sample sheet with sample data - before and after data

Absolutely. I've attached a sample spreadsheet containing two worksheets ("Original" and "Cleaned") and 9 cases. The material in the "Original" worksheet is uncleaned and not concatenated, while the data that appears in "Cleaned" has a standardized format.

The only thing difficult here is the addresses that have something like NW included. The rest are simply formulae. See the attached first sheet.

Thanks for the help. I knew how to do this using formulae, but I wanted to make the entire process automated (i.e., I paste the original data into the spreadsheet, press the hotkey for the macro, and voila!). The problem I've run into using formulae is that the number of cases varies every time, so there's no way to say exactly how many rows the formulae should extend through. One way to do this would be to have the cells with formulae right next to the cells with original data and double click the small box in the bottom right corner to apply the formulae through all the visible/used data, but I was hoping there was maybe some way for a macro to know exactly how many cases/rows of data there are each time and apply the formulae only to that number of cases/rows. I have other macros to fix issues in the addresses (e.g., "Nw" to "NW" and "1St" to "1st"), but I want to be able to make sure that those macros are being run only on cells that contain actual cases.

The problem I've run into with formulae is that, if I want to make sure that my formulae can apply to any number of cases, I simply extend them to about row 20,000 (assuming I'd never have more than 20k cases). In the event that there is no case beyond row 1200, however, the cells with formulae still register as "active" cells, when you "select all" for a paste. Does this make any sense?

try

Please Login or Register  to view this content.

You can use the macro above, and if that meets your needs, then great

Originally Posted by newnoise

!). The problem I've run into using formulae is that the number of cases varies every time, so there's no way to say exactly how many rows the formulae should extend through.

To solve this problem, I would just create a table. Add headings in row 1 for pasted data and the formulae. In row 2, leave blank spaces for the pasted data and add the formulae. Then format the table DATA/insert table. In his format, the table will auto extend the formulae to the same number of rows you have pasted data