Text in One Cell into Multiple Rows/Cells

[Submersed]

Hi All,

After a while Googling, I can't seem to find exactly what I'm trying to do.

I have a cell with some Text (With Line breaks etc) I want to be able to Copy and Paste the Text into Multiple Rows/Cells.

If I go into the cell and copy the Text (Not the cell) and paste - it works - But I need it in VBA as its looping through 4500+ rows

For Example: Cell A1

This is the text With Line Breaks etc 001 Line 002 Row

How do I copy this cell/Text in VBA and paste into 4 rows, so it ends up like this:

This is the text

With Line Breaks etc

001 Line

002 Row

Thanks in Advanced

 

[VBE313]

In cell B3, Here is the formula

=TRIM(MID(SUBSTITUTE($B2,CHAR(10),REPT(" ",LEN($B2))),($A3-1)*LEN($B2)+1,LEN($B2)))

In image, I have A$3, it should be $A3.


For better clarification, here is a link that helped me assist you. Split Multiple Text

 

[Submersed]

Thanks for this! Let me see if I can get it working.

I think I may have an issue as I don't know how many Lines are in the cell.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

20 09 15.xlsm
	A
1	This is the text With Line Breaks etc 001 Line 002 Row
2
3	This is the text
4	With Line Breaks etc
5	001 Line
6	002 Row
7
8
Text to rows

Cell Formulas
Range	Formula
A3:A8	A3	=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A$1,FIND("#",SUBSTITUTE(A$1&CHAR(10),CHAR(10),"#",ROWS(A$3:A3)))-1),CHAR(10),REPT(" ",100)),100)),"")

 

[VBE313]

Thanks for this! Let me see if I can get it working.

I think I may have an issue as I don't know how many Lines are in the cell.

You can just drag the number down to 100 and drag the formula down.

 

[Submersed]

This works perfectly for one cell, however, I have 4500 cells that need splitting. (If that makes any sense??) A1 to A4500

I had been working on some VBA code that loops through the cells, pressing F2, Ctrl + a, Ctrl + c - to copy the text and paste where needed....Not a great solution. Would be a lot better if a formula could manage it.

 

[Peter_SSs]

I had been working on some VBA code that loops through the cells

What about without looping? (Test with a copy of your data)

Sub Split_Text()
  Range("A1", Range("A" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(10)
End Sub

 

[sandy666]

you can try Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    STBD = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Raw", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),
    FilterBlank = Table.SelectRows(STBD, each ([Raw] <> ""))
in
    FilterBlank

it doesn't matter how many rows you have

Raw This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row

Raw This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row This is the text With Line Breaks etc 001 Line 002 Row