Macro to locate/modify text
 

Hello all,

I have an excel document that has a series of cells in an excel sheet
that have some data in them. Each cell has a format similar to this:

N/C 1) Sample text N/C 2) More sample text N/C 3) ........

Each cell could have up to 5 pieces of data in it. What I need to do
is have a macro that will step through each cell and locate each piece
higher than N/C 1) and insert an Alt+Enter before it. The resulting
cell would look like this:

N/C 1) Sample text
N/C 2) More sample text
N/C 3) ........

This would make it much easier to read and analyze. The reason I need
to do this is a report needs to be exported into excel on a regular
basis, and unfortunately there is no way to export the text in any
other way. Therefore, I need a macro that can clean it up a bit.

Can anyone help me with this?

Thanks in advance,

-NVB

Macro to locate/modify text
 

Sub fixData()
Intersect(Selection, ActiveSheet.UsedRange).Select
Selection.Replace What:="N/C", _
Replacement:=Chr(10) & "N/C", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
For Each cell In Selection
If Left(cell, 1) = Chr(10) Then
cell.Value = Right(cell, Len(cell) - 1)
End If
Next
Selection.WrapText = True
Selection.ColumnWidth = 50
Selection.EntireColumn.AutoFit
Rows.AutoFit
End Sub

--
Regards,
Tom Ogilvy


"Nate" wrote:

Hello all,

I have an excel document that has a series of cells in an excel sheet
that have some data in them. Each cell has a format similar to this:

N/C 1) Sample text N/C 2) More sample text N/C 3) ........

Each cell could have up to 5 pieces of data in it. What I need to do
is have a macro that will step through each cell and locate each piece
higher than N/C 1) and insert an Alt+Enter before it. The resulting
cell would look like this:

N/C 1) Sample text
N/C 2) More sample text
N/C 3) ........

This would make it much easier to read and analyze. The reason I need
to do this is a report needs to be exported into excel on a regular
basis, and unfortunately there is no way to export the text in any
other way. Therefore, I need a macro that can clean it up a bit.

Can anyone help me with this?

Thanks in advance,

-NVB