Extract Text From String that contains <cr> with a Macro

I have been given the task of populating a spreadsheet with contact information (e.g. street address, city, zip, phone, fax). I am looking up the info on a web site and copying it as formatted and pasting it into a cell of a spreadsheet. There are seven lines of data and data from six of them goes into a different cells. I also have to ignore some of the data.

I recorded my keystrokes into a Macro using Relative References. The data gets put in the correct cells but the macro also remembers all the data. This results in the same data in all the cells. Did I do something wrong? Is this possible using a Macro for this task? Also, is there anohter way? I thought of an array but am not skilled enough to know how to do this without spending too much time on it.

This entire job will take about 10 hours if toally manual. If I can shave off 5 or 6 hours, I'm all for it. So far, I have already spend an hour too much trying to figure out why the Macro isn't working *simle*

I tried this in Excel 2003 and 2007.

Any help would be appreciated.

Example of Cell text after paste from web, before Macro is run:

123 any street --> in cell a6
Anytown, IL 65656 --> ignore
555-666-6789 --> in cell a7
District Office:--> ignore
345 another street suite 111 --> in cell a9
Other Town, IL 65656 --> in cell a10, ignore ", IL" and put zip in a11
444-333-2121 --> in cell a12

Example data is in my original post. The text is red describes how I want to manipulate the data. Let me know if you need something else.

Originally Posted by napergman

Example data is in my original post.

What DGagnon means is post your spreadsheet that has the data in it and the macro so someone can help you.

Ok, now I understand. See the attached.

The input data is in Column G -- 7 lines of text in 1 cell (G24 and G25). I want to distribute the data to Columns H, J, K, L and M in the same row. See row 23 for desired output.

Ok, well it worked for one row -- same output as my macro. I may have done something wrong.
I copied your code and pasted it into my existing macro, replacing all of the existing macro text.
I put the cursor in the first cell and ran the macro. It populated the rest of the columns of that row and returned the cursor to the next row (correct cell). I ran the macro again and it duplicated the original data set even though the data in the starting cell was different. I then pasted new data into a cell a few rows down and ran the macro again. I got the same data set as the previous two macro runs. Was I supposed to do something different? I think I understand how your code works and it should be independent of the data.

Select the cell(s) in Col.G then run the code

Please Login or Register  to view this content.

Hello Jindon. Thank you for your solution. It basically works but I need a few other things. See my post from today at 3:23 pm. Can you assist? Thanks