Split Text Into Columns Excel VBA Macro

In this post we see how to split text into columns in Excel using VBA macros. The macro splits the text in one column into as many columns as dictated by the text separator (a comma, space, etc). In this example, we use the TextToColumns method of the Range object. But this can also be achieved using the Split VBA function to create an array and then write into columns.

Macro code:

  

  Sub SplitTextIntoColumns()
     
      Dim rng As Range
     
      If Selection.Columns.Count = 1 Then
          'assign the range with data to object variable rng
          Set rng = Range(Selection, Cells(Rows.Count, Selection.Column).End(xlUp))
   
           Selection.TextToColumns Destination:=rng, _
           DataType:=xlDelimited, Space:=True  
      Else
           MsgBox "Select only 1 column to split"  'if more than 1 column selected
       End If
  End Sub
 

Macro explained:

• We declared an object variable (rng) to store the range with data in the selected column to be split.
• The If statement is used to confirm that only one column was selected. The text to columns method only accepts text in one column. To split text in more than a column, additional code is needed (eventually, using a loop and the Split function).
• Then we apply the TextToColumns method to the Selection (selected column), and set the destination to the specific range to be split. The DataType can either be delimited (xlDelimited) by some character or arranged in columns of fixed width (xlFixedWidth). When delimited, additional parameters are required to specify the type of delimiter. In this example, we use the space as delimiter (Space:=True). But we could also use the Tab, Comma, Semicolon, Other (with OtherChar representing the character).
• That will split the text in the selected column into as many columns as parts separated by a comma.

 

This is how we split text into columns in Excel using VBA macros.

Other examples:

• How to Insert Picture From URL with Excel VBA macros
• How to Auto Fill Table Data with Excel VBA macros
  
• How to Combine All Open Workbooks with Excel VBA macros