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 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.

separate_carriage_return

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.

separate_carriage_return_02

VBA Code – separate line break data (multiple columns)

Take the above dependent table as example.

separate_carriage_return

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.

separate_carriage_return-002

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

separate_carriage_return-001

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
Wyman is a Human Resources professional based in Hong Kong, 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

9 thoughts on “Excel VBA separate line break data into different rows

  1. Thanks for this article. It was a life-saver. Had a 23,000 row file handed to be with multiple CR in one column. This did in 1 minute what would have taken days as the substitute function did not work.

  2. Hello! Your code almost worked for my situation, but not quite. Perhaps you can assist?

    I have 11 columns (A through K) and 44,000 rows of data that have entire rows with line breaks interspersed in my data. When I encounter a row with all line breaks, it’s as if the row should be split perfectly into two rows, so there is a one-to-one relationship with all eleven columns in these random rows.

    In your code, I added all the columns (not just B and D), but it didn’t work for some reason.

    As an added annoyance, these random line-break rows have different formatting of the data, where multiple zeros appear before the number (i.e. text). I’m not sure if this might be messing with the VBA code or not.

    I have a screenshot if needed.

    Thanks in advance for any help you might be able to provide!!

    1. Hi there,

      I have a similar situation to Nicole’s above and am getting an error when trying to run the code. I’m getting “Run-time error ‘5’: Invalid procedure call or argument” at the following line:

      upperRng.Value = Mid(currentrng.Value, 1, InStr(currentrng.Value, vbLf) – 1)

      Can you please help?

      Thanks!

  3. THANK YOU A LOT! I have been looking for a very long time to find this! I can’t believe I have to search through 20 google results before I found this website!

Leave a Reply

Your email address will not be published. Required fields are marked *