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

xlmaniac

Well-known Member
Joined
Jul 2, 2009
Messages
503
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Apr 8, 2009
Messages
17,649
xlmaniac,

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


We would like more information. Please see the Forum Use Guidelines in the following link:

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.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


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
Joined
Jul 2, 2009
Messages
503
Office Version
  1. 2010
Platform
  1. Windows
Dear Mick,
Thanks a lot for your reply & help provided.
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=A1Desired 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
Joined
Apr 18, 2011
Messages
36,684
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 2, 2009
Messages
503
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jul 2, 2009
Messages
503
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top