Excel VBA separate line break data into different rows

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

Wyman W

Compensation Survey Manager@Mercer

Wyman is human resources professional, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

You may drop me a message if you are interested in HR consultancy services from Mercer.