Creating a separate cell entry for every individual line in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a lot of cells of data with free text in them.

In order to use this data I need to create a separate cell for every line of
text within a cell.

The lines have been defined in the original cells using the Alt-Enter
function.

Does anyone have an idea of how to tackle this?

Any help much appreciated
David
 
It is pretty simple in a macro to find and extract the cells that hae the
carrriagge return. The problem is where do you put the new lines into the
worksheet. You could add it to the row bleow, but that cell may contain data
you don't want over-written. Inserting new cells may not be good either.
The new cells would add new rows and making some of the data in rows below
not being on the same row.

You need to give more definition of your worksheet layout before a macro can
be writen.
 
Joel

The format is simply a column (column B) of around 650 cells each with a
unique identifier in a separate column (column A)

It would be my intent to put the generated "expanded" cells in a separate
worksheet retaining their original but now multiple identifiers.

Is this clearer?

David
 
Try this code. to use macro
1) right click on tab on bottom of worksheet which normally says sheet1 or
you sheetname
2) Select Code
3) On VBA menu Insert - Module
4) Paste code below into module. remove > if the website adds
5) go to spreadsheet and run macro from Tools Menu - Macro - Macros -
Splittextt


Sub Splittext()

LF = Chr(10)

Lastrow = Range(Cells(1, 1), Cells(Rows.Count, 1)).End(xlDown).Row

LoopCount = 1
RowCount = 1
Do While LoopCount <= Lastrow

CellText = Cells(RowCount, 2)

If InStr(CellText, LF) > 0 Then

Do While InStr(CellText, LF)

LFPosition = InStr(CellText, LF)
FirstLine = Left(CellText, LFPosition - 1)
CellText = Mid(CellText, LFPosition + 1)

Cells(RowCount, 2) = FirstLine

Range(Cells(RowCount + 1, 1), Cells(RowCount + 1, 2)). _
Insert Shift:=xlDown
Cells(RowCount, 2) = FirstLine
RowCount = RowCount + 1

Loop

If (Len(CellText)) > 0 Then

Cells(RowCount, 2) = CellText
RowCount = RowCount + 1

End If

End If

LoopCount = LoopCount + 1
Loop

End Sub
 
You can break these into separate cells across a row using Data>Text to
Columns>De-limited by>Other>Alt + 0010(on the numpad)


Gord Dibben MS Excel MVP
 
Back
Top