# How To Extract Mutiple Line Items In A cell To Mutiple Rows Sequentially?

#### xlmaniac

##### Well-known Member
Dear All,
I do have a long column of almost 1000 rows which has multiple Line Items entered(with ALT+ENTER method) in a single cells.
The line items consist of both text & numbers in it.
I want to extract the line items entered in cells and put them to the next adjacent columns sequentially.
For example, I have the cells with multiple line items in Column A.
I would like to extract them from column A and put them in multiple cells under column B.
The line items of Cell A1 should first come and sit in B Column(irrespective of number of line items) first & after that that of A2 should come 2nd and sit and so on.
Is there any formula/code which can help in getting this done?
Thanks & Regards

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### MickG

##### MrExcel MVP
Try this:-
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG07Jan33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, vbLf)
Cells(c, 2).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
c = c + UBound(Sp)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### hiker95

##### Well-known Member
xlmaniac,

It would really help us if we could see examples of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

Last edited:

#### xlmaniac

##### Well-known Member
Dear Mick,
I pasted the code in the VBA module but could not figure out the function or the next course of action.
Pls let me.

Dear Hiker,
The following is the sample data set.
The first set of 2 sentences has been put in Cell A2.(Using ALT+ENTER method)
The second set of 3 sentences has been put in cell A3.(Using ALT+ENTER method)
The code/formula should extract the sentences from these 2 cells and put them sequentially across B2:B6.
This code or formula should be able to yield the same results across a set of any cells that I select.
Further the number of sentences across any cell would also vary from 1 sentences to any number.
Pls help.

 Data=A1 Desired Results=B1 Gillette, Whisper, Pampers, Olay, Supply Issue. Handwash 900ml Stock not Getting delivered.=A2 Gillette, Whisper, Pampers, Olay, Supply Issue.=B2 Ariel, Tide Fillrate is low. RBI FR is low due to year end. HUL Supply Issue.=A3 Handwash 900ml Stock not Getting delivered.=B3 Ariel, Tide Fillrate is low.=B4 RBI FR is low due to year end.=B5 HUL Supply Issue.=B6

<tbody>
</tbody>

#### Rick Rothstein

##### MrExcel MVP

Try this:-
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG07Jan33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, vbLf)
Cells(c, 2).Resize(UBound(Sp) + 1) = Application.Transpose(Sp)
c = c + UBound(Sp)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Here is another macro that should should also work...
Rich (BB code):
``````
Sub RearrangeCellData()
Dim Data As Variant
Data = Split("Desired Results" & vbLf & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), vbLf), vbLf)
Range("B1").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub
``````

#### xlmaniac

##### Well-known Member
Here is another macro that should should also work...
Code:
``````[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub RearrangeCellData()
Dim Data As Variant
Data = Split("Desired Results" & vbLf & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), vbLf), vbLf)
Range("B1").Resize(UBound(Data) + 1) = Application.Transpose(Data)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]``````

Dear Sir,
Thanks a lot for your input.
I have pasted the code in the VBA module & have saved the file in xlsm mode as well but the UDF is not coming.
Is there anything else that I need to carry out apart from the above?
Could you pls help me in getting this fixed?
Regards

#### xlmaniac

##### Well-known Member
Dear Sir,
I have run the macro & it is yielding the desired results
Thank you so much for your help in solving my problem.
Feel really proud to learn from the gurus like you.
with warm regards

Replies
4
Views
814
Replies
1
Views
431
Replies
9
Views
173
Replies
1
Views
794
Replies
1
Views
187

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,867
Messages
5,834,087
Members
430,260
Latest member
MANICX100

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back