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

[xlmaniac]

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

 

[MickG]

Try this:-

[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]

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

 

[xlmaniac]

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=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]

Try this:-

[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...

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]

Here is another macro that should should also work...

[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]

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