How to split the Cell Content

[akhendre88]

Hello,

I receive an auto generated sheet every day in which multiple lines are combined in only one cell.

Every line ends with either ' or ). My objective here is to get each line in different cell. How can I get this done?

Will split() be useful in this scenario or is there any other way?

Thank you.

 

[Rick Rothstein]

Hello,

I receive an auto generated sheet every day in which multiple lines are combined in only one cell.

Every line ends with either ' or ). My objective here is to get each line in different cell. How can I get this done?

Will split() be useful in this scenario or is there any other way?

Do you have only one column of data to process? If not, then what should be done with the other columns of data?

 

[akhendre88]

No, there will be only one column.

Currently, I have all the data in 'A1', and I want to split it in such a way that first line should go in A1, 2nd in A2 and so on...!!

Thank you.

 

[Rick Rothstein]

No, there will be only one column.

Currently, I have all the data in 'A1', and I want to split it in such a way that first line should go in A1, 2nd in A2 and so on...!!

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SplitDataInCellA1()
  Dim Arr As Variant
  Arr = Split(Range("A1"), vbLf)
  Range("A1").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]