This is a tedious bit of work, isn't it? Assuming all your addresses are just 3 lines long, it is fairly easy to revise the layout. Let us say the first name and address occupies the range A1 to A3. A1 is name, A2 is Address, and A3 is City State Zip (CSZ, in the trade). The next address occupies A4 to A6, and so on, down the column. If there is a blank row separating the data, we'll need to consider it as part of range, so that the first address would then be A1 to A4, etc.
Step 1: If you put the formula =A2 in cell B1, this will pull the address into the position you want. Put =A3 in cell C1, and your CSZ is now lined up with Name and address.
Step 2: If there is no blank row separating the address data, drag a selection rectangle to include cell B1 at upper left, down to and including cell C4 at bottom right. But if there IS a blank row, then go one row further, so that C5 is at bottom right. Basically you've highlighted a small block of cells. Now drag the selection rectangle by the little black square at the bottom right corner. Drag all the way down to the bottom of your data, then let go. You should now see all your name and address info as rows, with blank rows in between them.
Step 3:: Select columns B and C, copy, then paste SPECIAL, using the "values" option. This gets rid of the formulas, and replaces them with the actual data.
Step 4: Highlight ALL the data. Then sort it by either column B or C. This will isolate all the data with blank rows, and those rows may then be deleted. Presto - there is your list.
However, you now want to parse the CSZ data to create separate fields for City, ST and Zip. If it so happens that all addresses are from the same State, then simply set up a column with CA repeated on every row - a simple copy/paste (or highlight - drag, as above described) operation. But we'll assume there are various States.
Step 5: Copy paste the CSZ data to the next column to the right. You'll now have TWO IDENTICAL CSZ columns. The one on the right is our "Work" data. The original is just for safe-keeping.
Step 6: Highlight all (and ONLY) the Work data. Then use Find and Replace to replace any occurrence of a comma and a space with ONLY a comma. Thus any address such as
Then select "Text to Columns" under the Data menu item (it may be elsewhere in your Excel version). In the dialog, tic the box for delimited data, then in the next dialog specify the comma character as the delimiter. Click finish.
Step 7: You'll now see that city occupies a column of its own, and that State and Zip are together in column of their own. IF NOT, it means that you have some addresses with NO comma separating city from State. But let that alone, for now.
Step 8: Highlight the new State and Zip column. Using text to columns, again, specify the space character as the delimiter (be sure the delimited box is ticked). This will create a new column containing the zip, and cause the original column to have just the State abbreviation.
Step 9: Any conversion NOT made in step 7 may be isolated by resorting your list, specifying the last (zip) column as the sort criterion. Now inspect the address data that was faulty. If not too many, you can just fix them manually, so that all columns are properly filled. However, if there are just too many cases, then we can parse out the zip codes and put them where they belong, then come back to take care of city and State.
Step 10: Insert some extra empty columns to the right of the column with the data that did not convert previously. This will give us some wiggle room for the next series of operations.
Step 11: Because zip codes have 2 different formats (##### or #####-####) we'll need a way to detect which format is being used, and then apply appropriate parsing. This formula will do it:
=IF (ISERROR(SEARCH("-",D18,1)), RIGHT(D18,5), MID(D18, SEARCH("-",D18,1)-5,99))
Note that the above contains spaces to keep it all on one line here on Yahoo. If you use it, you'll need to get rid of the spaces. Basically the formula looks for the "-" character, found only in the 9 digit zip format. If found, it displays it. But if not, then it simply grabs the last 5 characters and displays them. The formula should be entered in the same column as the zips extracted above -where the zip code SHOULD be. The cell references in the formula should be changed to refer to the actual cell where the faulty City ST and Zips are found.
Now immediately copy the new zips and paste special - values so they become actual data.
Step 11: We'll use our new zips to get rid of the zips in the faulty address cells. Here is the formula:
=SUBSTITUTE(D18,F18,"")
This should be entered in the cells just to the right of the faulty data column. That space should currently be unoccupied. In the formula, D18 should be replaced with the cell in which the faulty data is contained. F18 should be replaced with the cell referring to the zip that goes with that data. The formula simply says to scan the cell for the zip code, and get rid of it. You will now have cells containing just city and State. Highlight them, copy and paste special, values, to create the actual data. THEN highlight and drag those cells on top of the faulty data, to replace old faulty with "new" faulty which will contain ONLY city and State.
Step 12: Referring now to your "new faulty data," extract the State abbreviation with this formula:
=RIGHT(G18,3)
This should go in the blank cells to the left of the new faulty data. G18 should be replaced by the cell with faulty data. The formula says to grab 3 characters from the RIGHT of that data. Why 3? Because there is a space at the end; you can't see it, but it probably is there. If the displayed result includes a space in front of the State abbreviation, then change the number to a 2. Do the copy/paste values thing again for your State abbreviations. Highlight and drag one column to the right, leaving blank cells between
Now we'll use another SUBSTITUTE formula to get rid of the State abbreviations which are still in the faulty data.
=SUBSTITUTE(D18,F18,"")
Again, replace D18 with a reference to the faulty data cell, and replace F18 with a reference to the State abbreviation cell. Copy/Paste special, again, then drag the results on top of the the faulty data.
Delete any blank columns, and your list should be complete.
BUT SUPPOSE some your original data has FOUR lines for name and address, instead of just 3? I can handle that faster than I can explain how to do it - and if such is the case, shoot me a message.
Hope that helps.