Splitting Multiline Cells into Separate Rows

[kcjt2009]

Hello Everyone,

I am having a heck of a time trying to figure out how to solve this problem of mine. I found a couple of similar questions asked, but there weren't any applicable answers.

I basically have a five column spreadsheet containing address information. While most cells only contain one piece of information, I have quite a few cells that have multiple lines of data in them because the individual has more than one address.

Here is an example. Note that Rows 1-2 contain single address info for each individual person. However, row 3 contains an individual with 3 pieces of address information in the four right hand columns:

Row 1: Unique ID | Name | Street Address | City | State | Zip Code
Row 2: Unique ID | Name | Street Address | City | State | Zip Code
Row 3: Unique ID | Name | Street Address | City | State | Zip Code

[carriage return in street address, city, state and zip cells]

--------------------------------- |Street Address | City | State | Zip Code

[carriage return in street address, city, state and zip cells]

--------------------------------- |Street Address | City | State | Zip Code

Row 4: Unique ID | Name | Street Address | City | State | Zip Code
Row 5: Unique ID | Name | Street Address | City | State | Zip Code


Please note that some individuals have 2-4 different addresses.

I would like to manipulate the 5 rows of example data above so that when I am done I have the following:

1. 7 rows of data (5 individuals with 7 total addresses)
2. Automatically copy and paste the unique id number and name into the two new rows that were created so that I have the following:

Row 1: Unique ID - 001 | Joe Chang | Street Address | City | State | Zip Code
Row 2: Unique ID - 002 | Joey Chung | Street Address | City | State | Zip Code
Row 3: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 4: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 5: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 6: Unique ID - 004 | Jane Derry | Street Address | City | State | Zip Code
Row 7: Unique ID - 005 | Julie March | Street Address | City | State | Zip Code

Your help is kindly appreciated. Thank you.

 

[kcjt2009]

Hiker

Thank you for taking the time to assist me. I really appreciate it.

Would it be possible for you to give me some tips on how to clean up this spreadsheet to make it more manageable? I believe there are a lot of carriage returns/line breaks and other assorted things that are getting in the way and each time I feel I kind of take a step forward, I hit a wall.

For example, can you show me how to get rid of the line breaks/carriage returns in those cells. I am able to seemingly only get two results. The first result is that I manage to replace the carriage return with a delimiter. But when I go into "Text to Columns" to manipulate it simply deletes the data after the delimiter instead of actually separating into a separate column. The other result will be the deletion of all carriage returns/line breaks so that I get the following:

San BernardinoSan Bernardino instead of the desired:

San Bernardino
San Bernardino

 

[kcjt2009]

bump one time to see if I can get any additional help on this.