Split multiline text to two column retaining the multiline row structure

[tulasiraman]

Hi,

My data looks like this (two rows, one column)

apple [fruit]
orange [color]
grapes [sour]

banana [yellow]
mango [sweet]

I want to convert them into (two rows, two column) as below

apple --------- fruit
orange ------- color
grapes ------- sour

banana ------- yellow
mango -------- sweet

Please provide me with a solution for this.

Thanks.

 

[Rick Rothstein]

Give this macro a try...

Sub SplitData()
  Columns("A").Copy Range("B1")
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "[*]", "", xlPart
    .Replace " " & vbLf, vbLf, xlPart
    .Cells = Evaluate("IF(LEN(" & .Address & ")="""","""",TRIM(" & .Address & "))")
  End With
  With Range("B1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "]" & vbLf & "*[", vbLf, xlPart
    .Replace "]", "", xlPart
    .Replace "*[", "", xlPart
  End With
End Sub

 

[Vanda_a]

Hi,

My data looks like this (two rows, one column)

apple [fruit]
orange [color]
grapes [sour]

banana [yellow]
mango [sweet]

I want to convert them into (two rows, two column) as below

apple --------- fruit
orange ------- color
grapes ------- sour

banana ------- yellow
mango -------- sweet

Please provide me with a solution for this.

Thanks.

Data - Text to Columns

 

[tulasiraman]

Thanks rick... it works exactly the way i want... Superb...

 

[tulasiraman]

Thanks rick... it works exactly the way i want... Superb...

 

[tulasiraman]

Hi Vanda... Your solution works for single line text only... Anyway Rick has provided with the solution... Thanks...

 

[Vanda_a]

Hi Vanda... Your solution works for single line text only... Anyway Rick has provided with the solution... Thanks...

Select column

 

[Rick Rothstein]

Select column

I think you are misreading the OP's data setup; this is all in a single cell, not three different cells...

apple [fruit]
orange [color]
grapes [sour]