VBA Split() Function – Split Cell Values with Carriage Returns to Multiple Columns using VBA Macro

Carriage returns 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 using VBA (or macro) procedures.

Split Text With Carriage Returns using VBA Macro

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.

Also Read: Find and Highlight Duplicate Values in Excel using VBA (macro)

VBA Procedure to Split Values to Multiple Columns

Before writing the Macro, we will first add a Button control on the sheet. 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 to it.

Option Explicit

Private Sub CommandButton1_Click()
    Call SplitText
End Sub

Sub SplitText()
    
    Dim str() As String

    If Len(ActiveCell.Value) Then      ' CHECK IF THE ACTIVE CELL IS NOT EMPTY.
    
        ' SPLIT THE ACTIVE CELL'S VALUE WITH LINE FEED (vbLf).
        str = VBA.Split(ActiveCell.Value, vbLf)
        
        ' REARRANGE TEXT TO MULTIPLE COLUMNS.
        ActiveCell.Resize(1, UBound(str) + 1).Offset(0, 1) = str
    End If
End Sub

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

Split Text using VBA Macro

Split Text using VBA Split() Function

The button’s click event calls the procedure SplitText(). First, it will check if the Active cell is not empty. The VBA.Split() function has two parameters, the first is the Active cells value and the second is vbLf (line feed) as a Delimiter. That is, each string or text, which is originally separated by a carriage returns are transformed with a line feed “vbLf”, and stored in a string array str(). A one-dimensional array that now holds each text with vbLf.

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

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

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

Also Read: Do you know how to Add Hyperlinks in Excel using a VBA macro?

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. It’s a simple procedure, which you can use in your application, in case you come across a situation like this. Don’t forget to select the cell before hitting the Split Text button.

However, you can also do it using Excel’s predefined formulas too. There is a procedure hidden inside the “Data” tab that you can find in the top menu. I’ll bring that procedure to you in my next article. Till then, thanks for reading.

← PreviousNext →



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts: