Excel Macro to Convert Text to Columns: VBA Guide and Tips

VBA & macros · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, a VBA macro can convert text in a selected column into separate columns by calling the range’s TextToColumns method. The example below splits comma-delimited text; select the source cells, run the macro, and make sure the cells to the right are empty before it writes the results.

What the macro does

Excel’s Text to Columns feature takes values stored in one column and divides them across neighboring columns. For example, a cell containing Riley Chen,Seattle,WA can become three cells: Riley Chen, Seattle, and WA. A VBA macro automates the same kind of split, which is useful when you repeat the task on similar data.

The macro here uses a comma as its delimiter and processes the selected range, provided that the selection is one continuous column. It does not search the workbook for data, decide which rows to process, or create a new sheet. Those are separate choices you can add to a larger automation routine.

A macro is not necessary for a one-time split. The built-in Text to Columns wizard is often easier for occasional work because it lets you preview the result and choose options interactively. VBA becomes useful when the same transformation needs to be repeated, or when it is one step in a broader workflow.

VBA macro for comma-separated text

This macro checks that you selected one continuous column, asks you to confirm, and then splits the selected cells at commas. Before running it, save a copy of the workbook and clear the cells to the right of the source data. Running a macro cannot be reversed with Ctrl+Z.

Sub SplitSelectedTextAtCommas()
    Dim sourceRange As Range

    ' Confirm that the selection is a worksheet range.
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set sourceRange = Selection

    ' TextToColumns needs one continuous source column.
    If sourceRange.Areas.Count <> 1 Or sourceRange.Columns.Count <> 1 Then
        MsgBox "Select cells in one continuous column.", vbExclamation
        Exit Sub
    End If

    ' Check that the destination cells to the right are available.
    If MsgBox("Split the selected cells at commas? Make sure cells to the right are empty.", _
              vbYesNo + vbQuestion, "Convert Text to Columns") <> vbYes Then Exit Sub

    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

The code uses a double quote as the text qualifier. In a quoted comma-separated value such as "Chen, Riley",Seattle, the comma inside the quotation marks is treated as part of the first field rather than as a separator. This is important for names, addresses, and other fields that may contain commas.

How to add and run the macro

  1. Open the workbook in the Excel desktop app, then save a backup copy. For a workbook that will contain VBA, save the working file as an Excel Macro-Enabled Workbook (.xlsm).
  2. On Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the code into the module window. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
  3. Return to the worksheet and select the source cells in one column. For example, select A2:A100 if those cells contain the delimited records. Include only the rows you want to split.
  4. Press Alt+F8, select SplitSelectedTextAtCommas, and choose Run. Read the confirmation message and proceed only if the cells to the right can be used for the split results.

On Mac, Excel supports VBA macros, but the keyboard shortcuts and editor access can vary with the keyboard and macOS settings. Use Excel’s Tools > Macro > Visual Basic Editor menu to open the editor, then add a module and paste the code. You can use Tools > Macro > Macros to run a macro; the Developer tab also provides access to Visual Basic and Macros. Excel for the web can open many macro-enabled workbooks, but it cannot run VBA macros, so use desktop Excel to execute this code.

Choose the right delimiter and data type

The delimiter is the character or boundary that marks where one field ends and the next begins. The sample macro turns on the comma delimiter and turns off tabs, semicolons, spaces, and the custom delimiter. To split tab-delimited data instead, change the options so Tab:=True and Comma:=False. For semicolon-separated data, use Semicolon:=True and set Comma:=False. Avoid enabling multiple delimiter options unless the data really uses all of them.

There are two common ways to structure text for splitting:

  • Delimited: A character such as a comma, tab, or semicolon separates each field. The macro uses DataType:=xlDelimited, which is appropriate for this format.
  • Fixed width: Fields begin at consistent character positions, whether or not a delimiter appears. Excel’s wizard can set break lines for this layout; a delimited macro is not a substitute for fixed-width parsing.

Spaces need special care. Turning on the space delimiter can split a full name into separate words, but it may also split a street address or a product description into too many pieces. For data such as Riley Chen,Seattle, keep the comma on and the space option off so the name stays together.

What happens to dates, zeros, and existing data

Splitting text is only part of the job: Excel may also interpret the resulting fields as numbers, dates, or other values. A postal code such as 00421 can lose leading zeros if Excel treats it as a number. Date-like text can also be interpreted according to the computer’s regional date settings. If preserving the original characters is essential, use the Text to Columns wizard’s column data format options to set the relevant output column to Text, or inspect and format the results carefully after conversion.

The sample macro does not specify a custom FieldInfo array to force each output column to Text. That keeps the code simple, but it means you should not assume every field will retain its original display or type. For important identifiers, test on a copy and verify the output, especially when leading zeros, long digit strings, or ambiguous dates are involved.

Also check the destination area before running the macro. TextToColumns writes the first result into the source column and subsequent results into columns to its right. Existing content there may be replaced, so do not treat the macro as a way to insert new columns automatically. Insert or move columns first if you need to preserve neighboring data.

When the built-in wizard is a better choice

For a one-off conversion, use Excel’s interactive command: select the source cells, then choose Data > Text to Columns. In the wizard, choose Delimited or Fixed width, specify the separators or break points, inspect the preview, and finish the conversion. The wizard is valuable when the input is unfamiliar because you can see how Excel will divide the fields before committing.

The wizard also makes it more straightforward to set a destination or choose a text format for particular output columns. That can reduce unwanted interpretation of codes and dates. A macro is a better fit when you already know the data pattern and want a repeatable action, but it should be tested with representative records before being used on a large or important dataset.

The macro and the wizard do not clean or validate the source data. They split according to the chosen rules. If some rows use commas and others use semicolons, or if a row has missing fields, the output may not line up consistently. Resolve those variations or define explicit handling for them before automating the conversion.

Common problems and practical checks

  • The macro reports that one column is required: Select a single continuous column. A selection spanning two or more columns, or multiple separate areas, is rejected by the example.
  • Everything stays in one column: Confirm that the text actually contains commas and that the macro’s Comma:=True option is enabled. A tab-separated file needs the tab option instead.
  • Fields split in the wrong places: Check whether the data contains quoted fields, extra delimiters, or a different separator. The double-quote text qualifier helps with quoted commas, but malformed or inconsistent quoting can still produce unexpected results.
  • Nearby values disappear: The output extends to the right of the source column. Undo is not available for a macro run, so restore the saved copy if data was overwritten.
  • The macro does not appear in the run list: Confirm that the code is in a standard module in the workbook, that the file is saved as .xlsm, and that macros are permitted under your organization’s security settings.

For a reliable routine, test with a small sample that includes ordinary rows and edge cases: a blank field, a quoted comma, a leading-zero code, and any date-like values. Compare the output with what you expect before using the macro on the full dataset.

Macro security and workbook handling

VBA can change workbook contents, so only enable macros in files from sources you trust. Excel’s security prompts and settings are there to help prevent unwanted code from running; do not lower macro security globally just to run a single workbook. If the workbook comes from another person or an external source, confirm what the code does before enabling it.

Keep the macro in a clearly named module and document any changes to its delimiters or assumptions. If other people will use the workbook, tell them that the procedure splits the selected column in place and requires clear cells to the right. A backup is particularly important because Ctrl+Z cannot undo the macro’s changes.

Frequently Asked Questions

Can Excel split text into columns automatically?
Yes. A VBA macro can call TextToColumns to apply a known delimiter to a selected range. The sample requires you to select the source column and run the macro; it does not automatically detect new data or run when a cell changes.

Can I use a different delimiter from a comma?
Yes. Change the delimiter arguments in the macro. For example, enable Tab:=True and disable Comma:=False for tab-separated text, or enable Semicolon:=True for semicolon-separated text.

Can the macro split data on spaces?
Yes, by setting Space:=True, but that splits at spaces throughout each value. It can break names and descriptions into more columns than intended, so use it only when spaces truly separate the fields.

Does TextToColumns overwrite the original text?
It writes the first field into the source column and places later fields in columns to the right. The original combined value is therefore replaced. Save a copy first and make sure the destination cells contain nothing you need to keep.

Can I run a VBA macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app for Windows or Mac to run the macro, or use the built-in Text to Columns command where available.

Why did Excel remove zeros or change a date?
Excel may interpret split fields as numbers or dates. Use the wizard’s column data format options to preserve sensitive fields as Text, and verify the results after conversion. The sample macro does not force every output field to text.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.