To run Excel’s Text to Columns tool with a macro, select the text column, then use VBA’s Range.TextToColumns method. The example below splits the selected cells at commas; change its delimiter settings to use tabs, spaces, semicolons, or another character.
What Excel Text to Columns does
Text to Columns takes text stored in one column and distributes its parts into neighboring columns. A cell containing Lee,Jordan,Seattle, for example, can become three cells: Lee, Jordan, and Seattle. It is useful for imported lists, copied reports, and other data where separators have been stored inside each cell instead of in separate fields.
Excel offers the feature as an interactive wizard and as a VBA method. The wizard is a good choice for an occasional split or when you want to inspect the settings. A macro is useful when the same transformation must be repeated, or when you want to make the delimiter and other choices consistent. The macro does not create a new kind of splitting engine: it automates Excel’s existing Text to Columns operation.
Set up and run a VBA macro
In Excel for Microsoft 365 on Windows, save a working copy first. Macros can overwrite cells and their actions cannot be reversed with Ctrl+Z. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the code in the workbook.
- Select the cells to split in a single column. The macro below expects one continuous selection, not multiple separate areas.
- Press Alt+F11 to open the Visual Basic Editor. You can also open it from the Developer tab by choosing Visual Basic.
- In the editor, choose Insert > Module, then paste the code in the new module.
- Return to Excel, keep the intended cells selected, and press Alt+F8. Select
SplitSelectedCellsByCommaand choose Run.
If the Developer tab is not visible on Windows, enable it through File > Options > Customize Ribbon and select Developer. Excel for Mac also supports VBA in the desktop app; use its Developer tab to open Visual Basic and the macro list. Keyboard shortcuts can differ by Mac keyboard and settings. Excel for the web cannot create or run VBA macros, so open the workbook in the desktop app to use this code.
Comma-delimited macro example
This macro checks that the selection is one continuous column, asks before proceeding, and then uses a comma as the delimiter. The output goes into the selected range, starting at its top-left cell, so existing values to the right may be replaced.
Sub SplitSelectedCellsByComma()
Dim sourceRange As Range
Dim answer As VbMsgBoxResult
' Make sure the selection is one continuous column.
If TypeName(Selection) <> "Range" Then Exit Sub
Set sourceRange = Selection
If sourceRange.Areas.Count <> 1 Or sourceRange.Columns.Count <> 1 Then
MsgBox "Select one continuous column of cells first."
Exit Sub
End If
' Confirm because Text to Columns can overwrite cells to the right.
answer = MsgBox("This may replace data to the right of the selection. Continue?", _
vbYesNo + vbExclamation, "Confirm Text to Columns")
If answer <> vbYes Then Exit Sub
' Split each selected cell at commas.
sourceRange.TextToColumns Destination:=sourceRange.Cells(1, 1), _
DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False
End Sub
Before running it, save a copy of the workbook and check that enough empty columns are available to the right of the selection. The confirmation is a reminder, not a full preview: it does not determine how many output columns the data will need or check that every destination cell is empty. If you are unsure, try the macro on a duplicate sheet or a small sample first.
How the delimiter settings work
The key method call is sourceRange.TextToColumns. The DataType:=xlDelimited argument tells Excel to find separators, rather than split at fixed character positions. In the example, Comma:=True turns on comma splitting, while the other named delimiter arguments are set to False. Set a different option to True to use that separator.
- For tab-separated data, set
Tab:=TrueandComma:=False. - For semicolon-separated data, set
Semicolon:=TrueandComma:=False. - For space-separated data, set
Space:=TrueandComma:=False. - For a character such as a pipe (
|), setOther:=Trueand provide the character with theOtherCharargument.
Choose only the separators actually used in the source. Turning on both comma and space, for instance, can split a full name into separate words as well as splitting its comma-separated fields. ConsecutiveDelimiter:=False treats adjacent delimiters as separate boundaries, so a blank field between two commas remains an empty output field. Setting it to True treats consecutive delimiters as one separator, which may be useful for repeated spaces but can discard the distinction between empty fields.
Text qualifiers and data conversion
The example uses TextQualifier:=xlTextQualifierDoubleQuote. This tells Excel to treat text inside double quotes as one field, even if it contains a comma. For instance, a row with "Lee, Jordan",Seattle is intended to produce a name field and a city field, rather than three fields. This is important when splitting conventional comma-separated data.
Text to Columns can also interpret values as numbers or dates. That conversion can be convenient, but it can damage identifiers that only look numeric. A code such as 00427 may lose its leading zeros if Excel interprets it as a number. Dates can also be interpreted according to regional settings, so a value such as 03/04/2026 may be ambiguous.
When preserving the original text matters, use the wizard’s column data format options to mark the relevant output column as Text, or configure the macro’s FieldInfo argument for the fields you need to preserve. Check the result after splitting, especially for dates, long numbers, account codes, and identifiers. If the data is important, keep the untouched source in a separate column or worksheet until you have confirmed the output.
Fixed-width data and the manual wizard
Not every text file uses a delimiter. Some reports place fields at fixed character positions, such as an ID in characters 1–8, a date in characters 9–16, and an amount after that. In this case, choose Data > Text to Columns, select Fixed width in the wizard, and place or adjust the break lines at the field boundaries. A macro can use DataType:=xlFixedWidth and a suitable FieldInfo setting, but the break positions must match the source layout.
The wizard is often the safer first step when the file format is unfamiliar: it lets you preview the proposed columns and adjust breaks before applying the split. To open it, select the source cells and choose Data > Text to Columns. On Windows, the wizard also provides settings for delimiters, text qualifiers, and output formats. The desktop Mac version has a similar Text to Columns workflow, though the ribbon layout can vary by release.
Common problems and practical safeguards
- Existing data disappears. Text to Columns writes results into cells to the right. Move existing data, choose a different destination in the wizard, or test on a copy before running the macro.
- Everything stays in one column. Check that the selected delimiter is actually present and that its option is set to
True. A pipe-delimited file will not split when only the comma option is enabled. - Names or phrases split too far. Avoid enabling a space delimiter when spaces belong inside a field. Use the separator that defines the actual boundaries.
- Leading zeros disappear or dates change. Excel may convert fields to numbers or dates. Set affected columns to Text in the wizard or use appropriate field formatting, then verify the result.
- The macro does not appear in the list. Confirm that the code is in a standard module, the procedure is a public Sub without arguments, and the workbook is open in desktop Excel. Macro security settings may also block macros.
- The workbook does not retain the macro. Save it as
.xlsm, not as a macro-free.xlsxfile. Excel may warn that VBA code will be removed if you save in a format that does not support it.
Only enable macros in workbooks you trust. VBA can change workbook data, and a macro-enabled file can contain code beyond the splitting procedure. If a workbook came from an unknown sender, do not enable its content just to use Text to Columns; the built-in wizard can perform the split without adding VBA.
When to use a macro, a formula, or Power Query
Use the built-in wizard for a one-time split, for previewing unfamiliar data, or when you prefer not to use macros. Use a macro when the same set of steps must be repeated on a predictable range and the workbook’s users are allowed to run VBA. A simple macro is not automatically safer or faster: it still needs the right selection, delimiter, output space, and conversion settings.
For a dynamic result that updates when source text changes, a formula may be more appropriate. Microsoft 365 and Excel 2024 include TEXTSPLIT, which can divide text using a specified delimiter; Excel 2021 and earlier do not include that function. For recurring imports that need repeatable cleanup, consider Power Query, available through Excel’s Data tools in supported desktop editions. It can split a column by delimiter and refresh a query when the source changes. The best option depends on whether the task is a one-off edit, a live worksheet calculation, or a recurring data-import process.
Frequently Asked Questions
Can I run Text to Columns automatically whenever I open a workbook?
It is possible to call a procedure from workbook events, but automatic execution is unnecessary for many split tasks and can surprise users or alter data without a clear prompt. A deliberate macro run is easier to review. If automation is required, design it with explicit source and destination ranges and test it on a copy.
Can a macro split data using more than one delimiter?
Yes. Set more than one delimiter option to True, such as comma and semicolon, if either character marks a field boundary. Be careful: every enabled delimiter is treated as a split point, so punctuation inside a value may create extra columns.
Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in Excel for Windows or Mac to run the macro, or use the web app’s available data tools or formulas for a non-VBA approach.
Why can’t I undo a macro with Ctrl+Z?
Running VBA generally clears Excel’s normal undo history, and Text to Columns changes the worksheet directly. Save a copy or duplicate the source data before running the macro; Ctrl+Z is not a reliable recovery method for macro actions.
Does Text to Columns delete the original text?
It replaces the source values with the first parsed field and writes later fields into columns to the right. If you need to preserve the original strings, copy them to another column or sheet first, or use a separate destination in the wizard.
Related
- Excel Import CSV as Text: Keep Every Value Unchanged
- Excel Import CSV Data: Steps, Settings, and Fixes
- Excel Flash Fill: How to Use It and Choose Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Text Function: Format Values and CellsIn Excel VBA, use the Format function—usually Format$—to turn a number or date into a formatted string; Excel’s…
- Excel VBA: How to Hide a SheetTo hide a worksheet with VBA, set its Visible property to xlSheetHidden, for example: ThisWorkbook.Worksheets("Report").Visible = xlSheetHidden. Use…
- Excel VBA: Keep a UserForm on TopTo keep an Excel VBA UserForm above other windows on Windows, call the Windows API function SetWindowPos with…
- VBA UserForm References: How to Show and Control FormsIn Excel VBA, reference a UserForm by its form class name, such as UserForm1, or create an explicit…
- Excel to PowerPoint VBA: How to Automate SlidesExcel VBA can automate PowerPoint on Windows: select worksheet ranges, copy them, create slides, and paste the content…
- VBA Loop Through a Range: Examples and Best PracticesTo loop through cells in Excel VBA, use For Each cell In worksheet.Range("A1:A10").Cells and place the action you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.