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.

 

[hiker95]

kcjt2009,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


We would like to see some actual raw data.

So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[kcjt2009]

I am using the 2013 Office Suite and Windows 8.

Below you will see a small sample with the header row and five additional rows of data. I am trying to change rows that look like the yellow one that I have highlighted. They can have anywhere from 2-4 addresses in a single row.

I am trying to extract the information to create rows below it while pushing the other text down without deleting it.



Excel 2012

	A	B	C	D	E	F
4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
6666	DAVIS,JONATHAN	3725 MORTENSEN RD	SAN LEANDRO	94578	CA
6666	DAVIS,JONATHAN	2400 LECONTE AVE	BERKELEY	94705	CA
7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Street[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Zip[/TD]
[TD="align: center"]State[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFF00"]6666[/TD]
[TD="bgcolor: #FFFF00"]DAVIS,JONATHAN[/TD]
[TD="bgcolor: #FFFF00"]3725 MORTENSEN RD


2400 LECONTE AVE[/TD]
[TD="bgcolor: #FFFF00"]SAN LEANDRO


BERKELEY[/TD]
[TD="bgcolor: #FFFF00"]94578




94705[/TD]
[TD="bgcolor: #FFFF00"]CA




CA[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]ID[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]Name[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]Street[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]City[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]Zip[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]State[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]4444[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]FLOWERS,LEONARD[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]141 89TH AVE APT # D
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]94621[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]5555[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]FLUKER,FREDRICK LEWIS[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]721 HILLSIDE ST[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]94605[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]6666[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]DAVIS,JONATHAN[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]3725 MORTENSEN RD


2400 LECONTE AVE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]SAN LEANDRO


BERKELEY[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]94578




94705[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]CA




CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]7777[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]FRANCO,BALTAZAR[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]17009 MONTROSE AVE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]SAN LORENZO[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]94580[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]8888[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]FRANKLIN,CURTIS EUGENE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]6234 15TH ST APT # 610
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]94612[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A10[/TH]
[TD="align: left"]4444[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]FLOWERS,LEONARD[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]141 89TH AVE APT # D
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D10[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]94621[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A11[/TH]
[TD="align: left"]5555[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]FLUKER,FREDRICK LEWIS[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]721 HILLSIDE ST[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D11[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E11[/TH]
[TD="align: left"]94605[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F11[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A12[/TH]
[TD="align: left"]6666[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]DAVIS,JONATHAN[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]3725 MORTENSEN RD
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D12[/TH]
[TD="align: left"]SAN LEANDRO
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E12[/TH]
[TD="align: left"]94578
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F12[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A13[/TH]
[TD="align: left"]6666[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]DAVIS,JONATHAN[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]2400 LECONTE AVE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D13[/TH]
[TD="align: left"]BERKELEY[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E13[/TH]
[TD="align: left"]94705[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F13[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A14[/TH]
[TD="align: left"]7777[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B14[/TH]
[TD="align: left"]FRANCO,BALTAZAR[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C14[/TH]
[TD="align: left"]17009 MONTROSE AVE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D14[/TH]
[TD="align: left"]SAN LORENZO[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E14[/TH]
[TD="align: left"]94580[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F14[/TH]
[TD="align: left"]CA[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A15[/TH]
[TD="align: left"]8888[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]FRANKLIN,CURTIS EUGENE[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]6234 15TH ST APT # 610
[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D15[/TH]
[TD="align: left"]OAKLAND[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E15[/TH]
[TD="align: left"]94612[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F15[/TH]
[TD="align: left"]CA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

kcjt2009,

Sample raw data:

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Street	City	Zip	State
2	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
3	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
4	6666	DAVIS,JONATHAN	3725 MORTENSEN RD 2400 LECONTE AVE	SAN LEANDRO BERKELEY	94578 94705	CA CA
5	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
6	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
7
8
Sheet3

After the macro:

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Street	City	Zip	State
2	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
3	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
4	6666	DAVIS,JONATHAN	3725 MORTENSEN RD	SAN LEANDRO	94578	CA
5	6666	DAVIS,JONATHAN	2400 LECONTE AVE	BERKELEY	94705	CA
6	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
7	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
8
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 02/14/2014, ME757628
Dim r As Long, lr As Long, s, i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 3).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 3), vbLf) Then
    s = Split(Cells(r, 3), vbLf)
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r + 1, 1).Resize(UBound(s), 2).Value = Cells(r, 1).Resize(, 2).Value
    Cells(r, 3).Resize(UBound(s) + 1).Value = Application.Transpose(s)
    s = Split(Cells(r, 4), vbLf)
    Cells(r, 4).Resize(UBound(s) + 1).Value = Application.Transpose(s)
     s = Split(Cells(r, 5), vbLf)
    Cells(r, 5).Resize(UBound(s) + 1).Value = Application.Transpose(s)
    s = Split(Cells(r, 6), vbLf)
    Cells(r, 6).Resize(UBound(s) + 1).Value = Application.Transpose(s)
  End If
Next r
Range("A2:A" & lr).Rows.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[kcjt2009]

Hiker

Thank you so much for taking the time to help me out with this. On the practice data, it worked great. It actually created a new row for each line of data including the carriage return in the cells, which I kind of prefer.

Now, when I take it to my actual data set, I am getting a Type 13 error and cannot figure out why. When I open the debugger it points to this line and highlights it in yellow;

Cells(r, 4).Resize(UBound(s) + 1).Value = Application.Transpose(s)


After I look at the highlighted line and close the debugger, I notice that the macro changed some of the rows of data the way it is supposed to...but it only changed about 200 rows of data out of around 3400 records. And interestingly enough, nearly all the records it changed were towards the end of the sheet (last names beginning with "w". Although there were a couple of strays and it did not change all the names starting with a "w".

I have tried a smaller data set from the same list and sometimes it works just fine without issue and sometimes it presents the Type 13 error but still manages to complete some of the row splits.

Not sure how to go about providing you with the info to help me fix this.

 

[hiker95]

kcjt2009,

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

If your actual raw is very different from your original screenshot(s), then, I would prefer the following:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[kcjt2009]

My apologies. Here is the link:

https://app.box.com/s/t1uisiembiryq6o8taby

I originally deleted the "Show On Map" text in the address cells, but left it in since the code you provided above basically put it on its own row and i could simply delete those later.

I am thinking that perhaps some rows have more than 4 lines of data in each cell, but I don't know how to read your code and wasn't sure how to tweak it to make it account for say a cell with 12 lines of text. And I am not even sure that that is the issue.

Thank you!

 

[hiker95]

kcjt2009,

Can I have another workbook, with the raw data in worksheet Sheet1, and, in worksheet Results, what the results should be for raw data rows 2, 17, 32, 45, 90 (raw data for these rows are shown below):

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Address	City	ZipCode	County
2	javascript: OpenDetail('18611314B2241') Row 2	AARON,JEFFREY DALE	25494 JASPER RD Show On Map	BARSTOW		SAN BERNARDINO
17	javascript: OpenDetail('38210041N4620') Row 17	ADELMAN,GARY JOEL	PO BOX 464 Show On Map 553 W VICTORIA CT Show On Map	SKYFOREST LAKE ARROWHEAD	92385 92352	SAN BERNARDINO SAN BERNARDINO
32	javascript: OpenDetail('18600227E3520') Row 32	AGUIRRE,RAFAEL	15814 MIRASOL CT POB 168 15814 MIRASOL CT Show On Map	HELENDALE HELENDALE	92342 92342	SAN BERNARDINO SAN BERNARDINO
46	javascript: OpenDetail('18602239G7839') Row 46	ALDANA,VICTOR DAVID	7149 N BARTON STREET Show On MapSpecific address not subject to disclosure	SAN BERNARDINO	92404	SAN BERNARDINO
90	javascript: OpenDetail('43611333B2584') Row 90	ANDERSON,JERRY JR	7054 JUNIPER AVE Show On Map 8783 SAN VINCENTE DR Show On Map	TWENTYNINE PALMS YUCCA VALLEY	92277 92284	SAN BERNARDINO SAN BERNARDINO
Sheet1

 

[kcjt2009]

Ideally it would look like the following:

https://app.box.com/s/t1uisiembiryq6o8taby

I am eventually going to delete all the additional rows that say "Show On Map".

 

[hiker95]

kcjt2009,

The structure of your actual raw data is nothing like what your original screenshot depicted.

I have not been able to come up with a macro that will give you the results you are looking for.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.