Split cell contents based on carriage returns?

[olly_w]

Hi

I have a dataset which contains cell entries with multiple pieces of data, separated by a carriage return (i.e. ALT+ENTER).

Is there a simple formula i can use to extract the cell contents based on the carraige return? For example, if i had Cell A which contained the following

123 OJW
222 LLS
333 MR ET

Could i split this one cell out into 3 different cell entries?

Any advice greatly appreciated

Ol

 

[Rick Rothstein]

Basically, I have a workbook with entries separated with carriage returns from cell H5 to cell H703. What I need to do is for each cell (i.e. H5 for instance), I need to split the cell out into the cells below (i.e. if there are 3 entries in cell H5, then I would need the macro to split the contents so that the first value is left retained in H5, and then value 2 in H6 and value 3 in H7.

The only caveat is that there will be values in the cells underneath H5, so when the macro runs and determines how many values their are in cell H5, then it will first need to insert that many rows (-1) below H5, and then split out the contents.

@olly_w,

Does this (interestingly enough) non-looping macro do what you want?

Sub OneLinePerRow()
  Dim LastRow As Long, DataStr As String, Data() As String
  Const DataColumn As String = "H"
  Const StartRow As Long = 1
  LastRow = Cells(rows.Count, DataColumn).End(xlUp).Row
  DataStr = Join(WorksheetFunction.Transpose(Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)), Chr(1))
  Data = Split(Replace(DataStr, vbLf, Chr(1)), Chr(1))
  Cells(StartRow, DataColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
End Sub

You might need to adjust the constant assignment in the Const statements to match your actual setup.

 

[blooddiamondkim]

@olly_w,

Does this (interestingly enough) non-looping macro do what you want?

Sub OneLinePerRow()
  Dim LastRow As Long, DataStr As String, Data() As String
  Const DataColumn As String = "H"
  Const StartRow As Long = 1
  LastRow = Cells(rows.Count, DataColumn).End(xlUp).Row
  DataStr = Join(WorksheetFunction.Transpose(Cells(StartRow, DataColumn).Resize(LastRow - StartRow + 1)), Chr(1))
  Data = Split(Replace(DataStr, vbLf, Chr(1)), Chr(1))
  Cells(StartRow, DataColumn).Resize(UBound(Data) + 1) = WorksheetFunction.Transpose(Data)
End Sub

You might need to adjust the constant assignment in the Const statements to match your actual setup.

This is almost exactly what I need for a project I've been stumped on, but I have 8 columns (A:H) and cannot figure out how to expand this macro through all of them. I've tried looking at constants for VBA but I am way out of my depth. Help is appreciated - even though I know this thread is from 2011!!!