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.

"DaveyC4S" wrote:

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

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

"Joel" wrote:

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.

"DaveyC4S" wrote:

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

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

"DaveyC4S" wrote:

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

"Joel" wrote:

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.

"DaveyC4S" wrote:

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

You can break these into separate cells across a row using DataText to
ColumnsDe-limited byOtherAlt + 0010(on the numpad)


Gord Dibben MS Excel MVP

On Thu, 15 Mar 2007 05:31:13 -0700, DaveyC4S
wrote:

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