How to split the lines in a cell into multiple rows.

**narayana_ou@yah** · 12-14-2007, 12:17 PM

I have a merged cell which contains a text with multiple lines.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.

**jasoncw** · 12-14-2007, 12:31 PM

Are the multiple lines due to just word wrap, or are there manual carriage returns (Alt+Enter) after each line?

Jason

**narayana_ou@yah** · 12-14-2007, 12:34 PM

They are just Word wrap

**jasoncw** · 12-14-2007, 12:55 PM

Assuming they are word wrapped in one column, but multiple rows, the following procedure should accomplish what you are requesting. It will unmerge the cells that are selected, and separate them into different rows.

Please Login or Register  to view this content.

HTH

Jason

**narayana_ou@yah** · 12-14-2007, 01:31 PM

Thaks for the help.
I want to split the cell into multiple rows.
Like If the cell "A1" Contains the following text
"I have a merged cell which contains a text with multiple lines and
I want to split the lines into multiple rows.Each line should come in a different row."
It should be split into two different rows "A1" and "A2" , such that in a cell only one row will be displayed.

**jasoncw** · 12-14-2007, 01:52 PM

So it is not a merged cell, but a single cell that you want to split into multiple rows, based on the number of word-wrapped rows in the one cell? If that's the case, I'm not really sure how to do that. Sorry.

**tariqbablu** · 08-22-2012, 05:28 PM

Try this,
A1=Original Data

[A2]
=LEFT(A1,LOOKUP(9.999E+307,FIND(" ",A1,ROW($A1:$A60))))

[A3]
=TRIM(SUBSTITUTE(A1,A2,""))

**surya5489** · 10-24-2013, 07:58 AM

Hi,

any solution for this issue.

I too have the same problem, I have a single cell which has wrapped data of many lines. Can i place each line in that cell in next row?

Thanks in Advance.

**arlu1201** · 10-24-2013, 09:24 AM

surya,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Pon Jeeva Baskaran** · 10-23-2014, 04:12 AM

Sub Unmerge()
With Selection
.MergeCells = False
.WrapText = False
.Justify
End With
End Sub

the above macro only separate 252 characters, however i have required to separate more than 252 characters in one cell.

**Pon Jeeva Baskaran** · 10-23-2014, 04:13 AM

Sub Unmerge()
With Selection
.MergeCells = False
.WrapText = False
.Justify
End With
End Sub

the above macro only separate 252 characters, however i have required to separate more than 252 characters in one cell.