How to Use Text to Column on Multiple Columns in VBA

Using Excel's VBA macro recorder to make a program that splits a single column into multiple columns involves not much more than a single mouse click. Splitting multiple columns presents a problem; the first column's separation will overwrite the text you want to split in subsequent columns. For that reason, the "TextToColumn" function will display an error message rather than split the column. By staggering the content to be split on multiple rows, you prevent this from occurring.

Instructions

1. Click the "Visual Basic" button in the Developer tab to enter the VBA programming environment, which lets you type and debug VBA programs.

2. Click the "Insert" tab's "Module" item to create a new window to enter programs. VBA will create the window.

3. Paste the following program into the window. This program uses the "TextToColumns" function to split text separated by spaces. The "Data Type" argument tells the function that you want to split text based on a particular character within the text, rather than based on a fixed number of characters per column. The "Space" argument of the function is set to "True," indicating that Excel should split the text based on the "Space" character.

   Public Sub TextToColumnsMultiple()

   Dim col, x

   For Each col In Selection.Columns

   Set x = col

   x.Select

   Selection.TextToColumns DataType:=xlDelimited, _

   TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _

   Semicolon:=False, Comma:=False, Space:=True, Other:=False, TrailingMinusNumbers:= _

   True

   Next

   End Sub

4. Click the "Excel" icon on the Windows task bar to return to Excel, then type a sentence with at least two space-separated words in any worksheet cell. Type another such sentence in the cell that's one down and one to the right of the first cell.

5. Click the first cell to select it, then press and hold the Ctrl key to tell Excel you want to select more than one cell.

6. Click the "Developer" tab's "Visual Basic" button to display a list of programs. Double-click the "TextToColumnsMultiple" item to run that program. The sentences you typed are now split into multiple columns, with one column per word.

References

• "Excel 2010 Bible"; John Walkenbach; 2010
• "Mastering VBA for Office 2010"; Richard Mansfield; 2010