Split cell values with Carriage Returns to Multiple Columns with VBA Split() function

Carriage return in Excel, also known as line breaks often occur in a cell when you enter values in a cell using a key combination of Alt+Enter, or you’ve copied the text from another source, which already had the line breaks. Here, in this article I’ll show you how to Split text (or values) with Carriage Returns to multiple columns (or split text into different cells) with the click of a button using VBA.

split text with carriage returns using vba

You might have figured out from the above image what I am trying to explain. It has two rows and each cell in the first column has values that contain carriage returns. I have created the carriage returns by typing a text followed by pressing the keys Alt+Enter. However, each value in the text has separate meaning. Therefore, it would be nice if I could separate the values to multiple columns that have meaningful headers.

🚀 Excel shortcuts that you should know.

👉 You may also like ... How to find and highlight duplicate values in Excel using a simple VBA procedure

Macro to Split Values to Multiple Columns

Before writing the Macro, we will first add a "Button" control on our worksheet. This is optional.

The click event will execute the splitting procedure. Press Alt+F8 to create a new Macro. You can name it whatever you like.

In the VBA section, open the "Project Explorer" (press Ctrl+r) and expand "Microsoft Excel Objects". Find Sheet1 and double clict to open it. Now add the below code.

Option Explicit

Private Sub CommandButton1_Click()
    Call splitText
End Sub

Sub splitText()
    
    Dim arr() As String
    
    Dim iRows, iTotalRows
    iTotalRows = Selection.Rows.Count       ' Selection will get all selected rows.
    
    For iRows = 1 To iTotalRows
        arr = VBA.Split(Selection.Cells(iRows, 1), vbLf)    ' split the selected text with line feed (vbLf).
        Selection.Resize(1, UBound(arr) + 1).Offset(iRows - 1, 1) = arr
    Next iRows
End Sub

Save the file and go back to sheet1. To see the result, first select a cell or multiple cells that you wish to split and press the button. The text will split into multiple columns.

split text to multiple columns using vba

Split Text using VBA Split() Function

The button’s click event calls the splitText() procedure. The VBA.Split() function has two parameters, the first is the value from the "selected cell" and the second is vbLf (line feed) as a "Delimiter".

Each string or text, which is originally separated by a carriage returns is transformed with a line feed "vbLf", and stored in a string array "arr()". It's a one-dimensional array that now holds each text with vbLf.

Note: To check the values in the "arr()" array, you can run a loop and see the output it debug mode (Ctrl+g). Like this...

Dim i As Integer
        
For i = 0 To UBound(arr)
    Debug.Print arr(i)
Next i

Ref: Learn more about Split() function on MSDN page.

Spread the Text across Multiple Columns

Once values are stored in the array, we will now spread or rearrange the text to multiple columns or cells next the active cell. To do this I am using the Resize() and Offset() method for the active cell.

The "Resize()" method takes two parameters to set a new range. The first is RowSize that defines the number of rows in the range. In our case, we need only 1 row (Try with more than 1 value and see the result). Second is the ColumnSize for the number columns in the range.

.Resize(1, UBound(str) + 1)

Using the "Offset()" method, I can decide from where to start rearranging the split values. Therefore, I have set the Offset to (0, 1). The value "0" for RowOffset will ask Excel to start the split from the same row. The value "1" for ColumnOffset indicates that the split will start from next column, which is adjacent to the active cell.

.Offset(0, 1)

Conclusion

There are many ways to split text with carriage returns to multiple rows with the help of VBA macro. This is a simple macro. Don't forget to select the cell(s) before hitting the Split Text button.

Bonus

You can also do this using Excel's built-in formulas, if in case you don't want to use VBA. There is a procedure hidden inside the "Data" tab in the top menu in Excel. Check this out... How to split texts in a cell into multiple cells using a formula.

← PreviousNext →