Author: Oscar Cronquist Article last updated on March 24, 2021

Text to columns method where to put the code

The TextToColumns method puts a cell that contains a string into substrings and distributes them horizontally using a delimiting character or based on a fixed width.

Excel Method Syntax

expression.TextToColumns (Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)

Arguments

parameter Description
Destination Optional. A Range object that points to a location on a worksheet.
DataType Optional. The text format of the source data.
TextQualifier Optional. Text qualifiers: single, double, or no quotes.
ConsecutiveDelimiter Optional. True - Consecutive delimiters as one delimiter. The default value is False.
Tab Optional. True - DataType be xlDelimited and the tab character is a delimiter. The default value is False.
Semicolon Optional. True - DataType be xlDelimited,  the semicolon is a delimiter. The default value is False.
Comma Optional. True - DataType be xlDelimited, the comma is a delimiter. The default value is False.
Space Optional. True - DataType be xlDelimited, the space character is a delimiter. The default value is False.
Other Optional. True - DataType be xlDelimited, the character specified by the OtherChar argument is the delimiter. The default value is False.
OtherChar Optional. Required if Other is True; the delimiter character when Other is True.
FieldInfo Optional. An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.
DecimalSeparator Optional. A decimal separator that Excel uses to recognize numbers. The default setting is the system setting.
ThousandsSeparator Optional. The thousands separator that Excel uses to recognize numbers. The default setting is the system setting.
TrailingMinusNumbers Optional. Negative numbers, in other words, numbers that begin with a minus character.

1. Record macro while running TextToColumns Wizard?

text to columns

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, across columns.

Where is this button?

  1. Go to tab "Data" on the ribbon.
  2. Press with left mouse button on the "Text to columns" button.
    A dialog box appears, see the animated image above.

What happens if we record a macro while performing this action? This is what the macro recorder returns:

Sub Macro1()
Selection.TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _ 
Other:=False, _
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
End Sub

Back to top

2. TextToColumns method - dot as delimiting character

Text to columns method dot

The image above shows a cell containing colors separated by a dot. The following VBA macro splits the string into substrings and distributes them to a cell each horizontally.

Sub Macro4()
Selection.TextToColumns Destination:=Range("B2"),  _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,  _
ConsecutiveDelimiter:=False,  _
Tab:=False, _
Semicolon:=False,  _
Comma:=False,  _
Space:=False,  _
Other:=True,  _
OtherChar :=".",  _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
End Sub

Text to columns method comma

Back to top

3. TextToColumns method - comma as delimiting character

Sub Macro3()
Selection.TextToColumns Destination:=Range("B2"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote,  _
ConsecutiveDelimiter:=True,  _
Tab:=False, _
Semicolon:=False,  _
Comma:=False,  _
Space:=True,  _
Other:=False,  _
FieldInfo :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1)), TrailingMinusNumbers:=True
End Sub

Back to top

4. Where to put the code?

Text to columns method where to put the code

  1. Press Alt + F11 to open the VIsual Basic Editor (VBE).
  2. Press with left mouse button on "Insert" on the top menu.
  3. Press with left mouse button on "Module" to create a new module.
  4. Copy and paste VBA code to the module.
  5. Return to Excel.

How to use the TextToColumns method

Text to columns: Split words in a cell [Array formula]