
This Excel VBA tutorial explains how to separate line break data into different rows.
You may also want to read:
Access Excel remove line break and carriage return
Excel VBA delimit Cell value into rows
Excel VBA separate line break data into different rows
Recently I received a report from a HR system which is claimed to be a standard report. In the report, some data are grouped into one Cell and are separated by line break.
I found this report format totally unacceptable. From the database perspective, each dependent is supposed to be stored as separate record, instead of storing it in a single record. The issue with this kind of report is that you cannot perform any VLookup, therefore it is necessary to split those grouped data into separate rows.
VBA Code – separate line break data
Public Sub separate_line_break()
target_col = "F" 'Define the column you want to break
ColLastRow = Range(target_col & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For Each Rng In Range(target_col & "1" & ":" & target_col & ColLastRow)
If InStr(Rng.Value, vbLf) Then
Rng.EntireRow.Copy
Rng.EntireRow.Insert
Rng.Offset(-1, 0) = Mid(Rng.Value, 1, InStr(Rng.Value, vbLf) - 1)
Rng.Value = Mid(Rng.Value, Len(Rng.Offset(-1, 0).Value) + 2, Len(Rng.Value))
End If
Next
ColLastRow2 = Range(target_col & Rows.Count).End(xlUp).Row
For Each Rng2 In Range(target_col & "1" & ":" & target_col & ColLastRow2)
If Len(Rng2) = 0 Then
Rng2.EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
Explanation of VBA code – separate line break data
First of all, user defines which column that contains line break. There are several types of line break
1) carriage return – vbCr
2) line feed – vbLf
3) carriage return + line feed – vbCrLf
I used vbLf in my example. The Macro loops through the first Cell (B1) of the defined column down to the last Cell (B5). If any Cell contains line break then it copies the whole row and insert a new row. As a new row is inserted, the last Cell of the looping range is also changed.
Finally change the upper row Cell value to the Cell value before the first line break, and the lower Cell is changed to the Cell value after the first line break. The same repeats down the Cell.
VBA Code – separate line break data (multiple columns)
Take the above dependent table as example.
If there is another column called Dependent ID, an ID to identify each dependent (one to one relationship with Dependent), then my previous Macro will not work.
For multiple column split, you can use the below Macro. Note that those columns you want to split must have one to one relationship. For example, if there are two names in B2, then D2 must have two corresponding values.
Public Sub separate_line_break()
colArray = Array("B", "D") 'Define what columns you want to split
check_col = colArray(0)
ColLastRow = Range(check_col & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For Each Rng In Range(check_col & "1" & ":" & check_col & ColLastRow)
If InStr(Rng.Value, vbLf) Then
Rng.EntireRow.Copy
Rng.EntireRow.Insert
For i = 0 To UBound(colArray)
c = colArray(i)
Set currentrng = Range(c & Rng.Row)
Set upperRng = currentrng.Offset(-1, 0)
upperRng.Value = Mid(currentrng.Value, 1, InStr(currentrng.Value, vbLf) - 1)
currentrng.Value = Mid(currentrng.Value, Len(upperRng.Value) + 2, Len(currentrng.Value))
Next i
End If
Next
ColLastRow2 = Range(check_col & Rows.Count).End(xlUp).Row
For Each Rng2 In Range(check_col & "1" & ":" & check_col & ColLastRow2)
If Len(Rng2) = 0 Then
Rng2.EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
Result
Line break based on other characters
If you want to break the line based on other characters instead of space, define your own delimiter using the below code. The below example breaks the line by colon.
Public Sub separate_line_break() target_col = "C" 'Define the column you want to break delimiter = ";" 'Define your delimiter if it is not space ColLastRow = Range(target_col & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For Each Rng In Range(target_col & "1" & ":" & target_col & ColLastRow) If InStr(Rng.Value, delimiter) Then Rng.EntireRow.Copy Rng.EntireRow.Insert Rng.Offset(-1, 0) = Mid(Rng.Value, 1, InStr(Rng.Value, delimiter) - 1) Rng.Value = Mid(Rng.Value, Len(Rng.Offset(-1, 0).Value) + 2, Len(Rng.Value)) End If Next ColLastRow2 = Range(target_col & Rows.Count).End(xlUp).Row For Each Rng2 In Range(target_col & "1" & ":" & target_col & ColLastRow2) If Len(Rng2) = 0 Then Rng2.EntireRow.Delete End If Next Application.ScreenUpdating = True End Sub
Outbound References
http://stackoverflow.com/questions/10024402/how-to-remove-line-break-in-string
Comments are closed.