How To Separate Address List From One Column Into Multiple Columns

How To Separate Address List From One Column Into Multiple Columns In Excel. http://bit.ly/ExcelProductivityHacks - only $19 to learn from me how to finish your projects in minutes instead of hours. When you have your addresses database in MS Word and it is formatted like this Client #1 5005 S Cooper St Ste 183 Arlington TX 76017-8600 Or in MS Excel and it is all within one cell in multiple lines, which looks just like the example above, you need to use a couple of formulas to break it out into several columns - 5 in this example. First I need to get rid of multiple lines within one cell. To do that I will select the column with address list, copy it, go into Word, paste special unformatted text. Now I need to get rid of double quotation marks before and after each address record. I will hold Ctrl key, push H key and type double quotation mark in the Find What field. I will not put anything into Replace With field, and click Replace All. I see a lot of extra spaces between the lines, to get rid of them I will go into Excel again. So I select everything, copy, go into Excel and paste it. With my column still selected I to Data - Filter. Then I click on the drop down in the first cell and unselect Blanks - they are always listed at the bottom of the list. I copy the whole column, go back into Word, where I still have all my addresses selected. I paste special - unformatted text. Now I do not have extra spaces between lines. My next step is to take my list into Excel again. Copy, paste in a new spreadsheet. My addresses are still in one column but they are broken into 3 rows each. I need them in 3 columns though. It is really easy with the first address, but for the rest of the records I will use OFFSET formula. It will read 3 ways for 3 rows of the address =OFFSET($A$1,$B2,0) - should go into cell C2 =OFFSET($A$2,$B2,0) - should go into cell D2 =OFFSET($A$3,$B2,0) - should go into cell E2 where column B has quantity of rows to offset the database by. I type 3 in cell B2, and =B2+3 in cell B3. Then I will drag this formula all the way down to row 108 because I have 108 records in my list of addresses. I will select cells C2 through E2 and drag OFFSET formulas down to row 108 as well. My next step is to get rid of formulas in columns C, D, and E so I then can split city, state and zip into 3 different columns. I don't need formulas for that, I just need values, that is why I will select columns C, D, and E, copy them, and then paste special values. Now my records in column E are inconsistent - some of them have a dash and 4 digits after the five digit zip code, some don't. I need to get rid of the dashes and 4 digits after them so I can then easily separate city, state and zip into 3 columns. I select column E, hold Ctrl, push H key on the keyboard to open up Find and Replace dialog box. I will type -**** into Find What field and will not make any changes in field Replace with and click Replace All. This removes unneeded parts of zip all at once. I will then type this formula =LEFT(E1,LEN(E1)-9) into cell F1. This formula pulls city out of cell E1. Now I need state and zip. For that I will use this formula =RIGHT(E1,8) in cell G1. I will now drag my formulas in cells F1 and G1 all the way down to row 108. To get rid of formulas so I can work with values in column G I will select columns F and G, copy, paste special values once again. The last step is to separate state and zip. To do that I select column G, go Data on my ribbon, click Text To Columns and choose either Delimited or Fixed width - in this case both will work, and click Finish. Now all parts of my list are in separate columns, and all I have to do is delete unneeded columns, insert a header row and type Client Name, Address, City, State, Zip. I hope this is helpful for you, especially if you have several thousands of records you need to separate into multiple columns from one. Here are more useful videos in my channel http://youtu.be/9u09QVbVRuc http://youtu.be/ZlPRFsQbNig http://youtu.be/KYOmtd_Jvis http://youtu.be/snvPbFo1snU http://youtu.be/yWXoOFp8RJY http://youtu.be/EN6FojfJ20c http://youtu.be/8Hxpb9Um7ro http://youtu.be/xUDUByA1pxw http://youtu.be/9qVRxDXVLy8 http://youtu.be/UYVksaUvgA4 http://youtu.be/XurlWclYStM http://youtu.be/ZVGP9_uqsns http://youtu.be/qU7uASqclvg http://youtu.be/UYwjcb9McJs http://youtu.be/0zbLREyuM8c https://youtu.be/HfkfWiGynBY