From a report, the data within one column will have multiple line breaks within the cell which I parsed out via marcro.
Here is an example the layout of the mentioned cell:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
</tbody>[/TABLE]
I have the following macro set up and it works for the happy path
What I need help with are the two different scenarios:
1 - One row will have 3 types listed out, and the following row will have 2 types listed out. Is there a way to make an iteration to make it separate?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
[/TD]
[/TR]
</tbody>[/TABLE]
2 - If a cell has incorrect line breaks
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
</tbody>[/TABLE]
Here is an example the layout of the mentioned cell:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
</tbody>[/TABLE]
I have the following macro set up and it works for the happy path
Code:
Sub splitText()
Application.ScreenUpdating = False
'splits Text active cell using ALT+10 char as separator
Dim splitVals As Variant
Dim totalVals As Long
Dim i As Integer
'Add columns to avoid overwriting data
Columns("D:O").Select
Selection.Insert Shift:=xlToRight
Range("C2").Select
For i = 1 To 1000
splitVals = Split(ActiveCell.Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
ActiveCell.Offset(1, 0).Activate
Next i
'Delete blank columns
Columns("E").EntireColumn.Delete
Columns("F").EntireColumn.Delete
Columns("H").EntireColumn.Delete
Columns("J").EntireColumn.Delete
Columns("D:K").ColumnWidth = 20
'Add Column Headers
Range("D1").Select
ActiveCell.FormulaR1C1 = "Full Text"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Type (1)"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Type (1) Risk"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Type (2)"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Type (2) Risk"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Type (3)"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Type (3) Risk"
'Remove unnecessary wording in each cell
Columns("D").Replace What:="Full Text: ", Replacement:=""
Columns("E").Replace What:="Description: ", Replacement:=""
Columns("F").Replace What:="Type (1): ", Replacement:=""
Columns("G").Replace What:="Type (1) Risk: ", Replacement:=""
Columns("H").Replace What:="Type (2): ", Replacement:=""
Columns("I").Replace What:="Type (2) Risk: ", Replacement:=""
Columns("J").Replace What:="Type (1): ", Replacement:=""
Columns("K").Replace What:="Type (1) Risk: ", Replacement:=""
Application.ScreenUpdating = True
End Sub
What I need help with are the two different scenarios:
1 - One row will have 3 types listed out, and the following row will have 2 types listed out. Is there a way to make an iteration to make it separate?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
[/TD]
[/TR]
</tbody>[/TABLE]
2 - If a cell has incorrect line breaks
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Full Text: Here is the full sentence
Description: Here is the full description for support.
Type (1): Color
Type (1) Risk: High
Type (2): Size
Type (2) Risk: Low
Type (3): Volume
Type (3) Risk: High
[/TD]
[/TR]
</tbody>[/TABLE]