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