Converting paragraphs to seperate columns

  • Thread starter Thread starter awren
  • Start date Start date
A

awren

Hi Forum,

I have been supplied some data which has multiple paragraphs per cell
I need to split each paragraph out into a seperate cell so that I ca
use the data properly. When you look at the contents of the cell th
paragraph is shown by two funny squares which I believe to be carriag
returns.

Can anyone help?

Cheers in advance.

Ale
 
First, get a copy of Chip Pearson's CellView Addin. It'll help you determine
what those funny characters are (and leave no doubt):

http://www.cpearson.com/excel/CellView.htm

Second, run a macro to change those funny squares to a unique character--not
used in any cell.

This'll change CrLF to a vertical bar:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10) & Chr(13))

myGoodChars = Array("|")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

Now go back to your data (one column at a time) and do Data|Text to columns,
choose delimited by that vertical bar. Make sure you leave enough empty columns
for each field!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Hello

I finally used the macro shown below to replace the characters 013 (CR
with a hash (so that I can then use the text to colums function)

However the problem I have is that I have got 2 sets of codes to dea
with e.g.

First line of text 013 010
013 010
Second line of text 013 010
013 010
Third line of text

Has anyone got any ideas how i might modify the code below to replac
013 & 010 with 035 (hash) and then ignore the second set (or replac
013 & 010 & 013 & 010 with 035)

Sub TrimALL()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

Selection.Replace What:=Chr(13), Replacement:=Chr(35), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Su
 
Awren,

You need to do a replace on a cell by cell basis using the VBA Replace
method, which allows you to specify the number of replacements to do:

Sub TrimALL2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
'Only replace the first instance of char 13
cell.Value = Replace(cell.Value, Chr(13), Chr(35), 1, 1)
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
Bernie
MS Excel MVP
 
Hello,

Thanks for that.

Just a quick question but it looks like what you have suggested wil
only do one replacement per cell. I think I need it to replace each od
numbered occurance as they always appear in groups of two (see example)

Any suggesions?

Ale
 
Alex,

I don't see an example, but this code will replace the 1st and 3rd
occurences of char(13):

Sub TrimALL1And3()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
'Replace the first instance of char 13
cell.Value = Replace(cell.Value, Chr(13), Chr(35), 1, 1)
cell.Value = Application.Trim(cell.Value)
'Replace the third instance of char 13 (now the second)
cell.Value = Left(cell.Value, InStr(1, cell.Value, Chr(13))) _
& Replace(cell.Value, Chr(13), Chr(35), _
InStr(1, cell.Value, Chr(13)) + 1, 1)
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH,
Bernie
MS Excel MVP
 
Hi Bernie,

Thanks for your help here. My example is shown below.

First paragraph of text [013] [010]
[013] [010]
Second line of paragraph [013] [010]
[013] [010]
Third line of paragraph

There is not always the same number of paragraphs. Ideally what I woul
like to do is strip the first line to one cell, then every thing in th
middle (could be multiple paragraphs). Finally the last line needs t
go in the third cell.

Thanks

Ale
 
Alex,

The code below will replace the first and last instance of char 13. Requires
Excel 2000 or later, due to the InStrRev function.

HTH,
Bernie
MS Excel MVP

Sub TrimAllFirstAndLast()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
'Replace the first instance of char 13
cell.Value = Replace(cell.Value, Chr(13), Chr(35), 1, 1)
cell.Value = Application.Trim(cell.Value)
'Replace the last instance of char 13
cell.Value = Left(cell.Value, InStrRev(cell.Value, Chr(13))) & _
Replace(cell.Value, Chr(13), Chr(35), _
InStrRev(cell.Value, Chr(13)) - 1, 1)
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Back
Top