Excel Text to Columns: How to Enter a Delimiter

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, go to Data > Text to Columns, choose Delimited, and click Next. Select a listed separator, or select Other and type your custom delimiter in its box; check the preview before clicking Finish.

Where to enter a delimiter

The delimiter is the character Excel looks for to decide where one piece of text ends and the next begins. In the Text to Columns wizard, the entry box for a custom delimiter appears on the second screen, after you choose Delimited and click Next. It is beside the Other option, not on the first screen and not in a cell on the worksheet.

For example, if a cell contains Riverside|CA|92501, choose Other, type a vertical bar (|) in the box, and inspect the preview. Excel should show three fields: Riverside, CA, and 92501. The separator character itself is not included in the resulting fields.

The wizard also offers checkboxes for Tab, Semicolon, Comma, and Space. Choose the checkbox that matches the character between the values. You can select more than one delimiter type if the data genuinely uses different separators, though selecting extra types can split values in unintended places.

Run Text to Columns step by step

Text to Columns converts text in one column into values spread across adjacent columns. It does not normally add or remove rows. Because it writes into cells to the right of the selected source, check that those cells are available before running it.

  1. Make a copy of the workbook or duplicate the source column if the original text must be preserved.
  2. Select the cells containing the text. To process a whole column, select the relevant data range rather than unrelated cells below it.
  3. Open Data > Text to Columns on the ribbon.
  4. In the Convert Text to Columns Wizard, select Delimited and click Next. Fixed width is for splitting at positions, not at a character separator.
  5. Select the appropriate delimiter checkbox, or select Other and enter the custom character. Use the preview to confirm the proposed column breaks.
  6. Review the remaining options, set a destination if needed, and click Finish. If Excel asks whether to replace existing data, stop and check the destination before confirming.

After conversion, review several results, including rows with short or unusually formatted values. Text to Columns applies the split to the selected rows, and different rows can produce different numbers of populated output columns. A row with no separator may simply remain as one value in the first output column.

Choose the right delimiter for your data

Use the character that consistently separates fields, not necessarily the character that appears most often in the text. A comma-delimited list such as Jordan Lee,Seattle,WA can be split on commas. A tab-delimited export should be split on Tab, even if the gaps look like spaces on screen. Tabs and multiple spaces can be difficult to distinguish visually, so the preview is useful.

  • Comma: Common in lists and exported data, but risky when a field itself contains commas, such as Seattle, WA.
  • Tab: Often used when copying tables or exporting records. A tab may not be visible in the cell contents.
  • Semicolon: Used in some data exports and regional formats; select it only if it separates the fields in your source.
  • Space: Useful for simple text such as first and last names, but every space becomes a break. A person with a middle name or a multiword surname may end up in extra columns.
  • Other: Useful for a character such as a pipe, slash, colon, or hyphen. Type the actual separator character in the box.

One delimiter setting applies throughout the selected data. If commas separate the fields but some rows use semicolons, selecting both might help only if neither character occurs inside a field. Otherwise, standardize the source first or process the different formats separately.

What the text qualifier does

The Text to Columns wizard includes a Text qualifier setting, commonly shown with a double-quote character. A qualifier tells Excel that delimiter characters inside a quoted field should be treated as part of that field rather than as a boundary. For instance, a comma-separated line might contain 42,"Portland, Oregon",West. With the double-quote qualifier selected, the comma inside the quoted place name is not treated like the two commas separating the three fields.

This option matters when the source follows a consistent quoted-data convention. It does not mean that every quotation mark in ordinary text will be interpreted as a protective wrapper in every situation. If the preview splits a quoted value incorrectly, check the selected qualifier and whether the source uses quotation marks consistently. Do not remove punctuation or quotes from the original data unless you know they are unwanted.

Preview, destination, and data types

The preview is a practical check, not just a visual extra. Look for the expected number of columns, intact names and addresses, and any values that have been divided in the wrong place. If the preview shows too many columns, an extra delimiter may have been selected, or the delimiter may also appear inside the fields. If it shows too few, verify the character entered under Other and check for spaces or tabs that only look like the expected separator.

By default, Excel places the split results starting at the selected cells. If the neighboring columns contain information, choose a different cell in the wizard’s Destination box, or move the existing data first. Writing results over occupied cells can replace them; undo may restore a recent operation, but it is safer to prevent the overwrite.

The wizard also lets you set a column’s data format. This can be important for postal codes, product IDs, or account numbers that begin with zero. If Excel treats such a field as a number, leading zeros may disappear. Set that output column to Text when the characters are an identifier rather than a number for calculation. Dates and other numeric-looking values can also be interpreted according to Excel’s regional settings, so check the output before relying on it.

Common problems and how to fix them

  • Nothing splits: Confirm that Delimited is selected and that the selected delimiter exactly matches the source. A visually similar character, such as a hyphen and an en dash, is not the same delimiter.
  • Values split too far: Look for the delimiter inside a field, such as commas in a city-and-state phrase, or clear delimiter checkboxes that are not needed. Consider whether a text qualifier applies.
  • Names split into too many columns: Splitting on every space is often unsuitable for full names because middle names and multiword surnames contain spaces. Use a more dependable separator if one exists, or review the results manually.
  • Leading zeros disappear: Set the relevant output column to Text in the wizard before finishing. Formatting the cells as Text afterward does not necessarily restore zeros Excel has already removed.
  • Existing data vanishes: The output overlapped occupied cells. Undo immediately if appropriate, or restore from a saved copy, then rerun with a clear destination.
  • Rows produce inconsistent results: Some records may have missing delimiters or an extra delimiter inside a field. Inspect those source rows rather than assuming every row follows the same structure.

When Text to Columns is not the best option

Text to Columns is convenient for a one-time split, but it changes the worksheet cells and does not keep a live connection to the original text. If the source will be updated repeatedly, consider Power Query: in Excel for Microsoft 365 on Windows, select the data and use Data > From Table/Range to open the Power Query Editor, then use the split-column controls and load the result back to Excel. The exact controls depend on the data and the split rule, but a query can be refreshed after its source changes.

For a formula-based split that updates when the source cell changes, Excel for Microsoft 365 and Excel 2024 support TEXTSPLIT. With the text North||West||Sales in cell A2, the formula =TEXTSPLIT(A2,"||") splits at the two-character delimiter and returns North, West, and Sales into adjacent cells when space is available. This is a useful alternative when the delimiter contains more than one character: Text to Columns’ Other box is designed for a single custom character, whereas TEXTSPLIT accepts a text delimiter. If you use an older Excel version without TEXTSPLIT, Power Query or a careful Find and Replace approach may be more suitable.

For an occasional cleanup, the wizard is usually simpler than a formula or query. For regularly arriving files, repeatable transformations, or multi-character separators, choose a method that can be refreshed or adapted without manually rerunning every step.

Windows, Mac, and Excel for the web

The instructions above describe Excel for Microsoft 365 on Windows. Excel for Mac also has a Text to Columns command on the Data tab, although the ribbon layout and wizard presentation can differ by release. Look for Data > Text to Columns, choose Delimited, and use the delimiter controls and preview. Excel keyboard shortcuts can also differ on Mac; using the ribbon avoids relying on a Windows-only shortcut.

Excel for the web has a different feature set and its available data tools may not match the desktop wizard in every account or release. If Text to Columns is not available in the web ribbon, open the workbook in the Excel desktop app or use an available alternative such as a supported formula or Power Query workflow. Avoid assuming that a menu path or shortcut from Windows desktop Excel will be identical in a browser.

Frequently Asked Questions

Where do I type a custom delimiter in Excel?
Open Data > Text to Columns, choose Delimited, and click Next. Select Other and type the separator character in the box beside it.

Can I enter more than one character as the delimiter?
The Other box in Text to Columns is intended for a single custom character. For a multi-character separator, use a method such as TEXTSPLIT in Microsoft 365 or Excel 2024, or transform the data with Power Query.

Can Text to Columns split on a space?
Yes. Select Space on the Delimited screen. Excel will split at spaces throughout the selected text, so this can divide middle names or multiword addresses into additional columns.

Does Text to Columns overwrite the original data?
It uses the selected column as the starting point for the output and fills cells to the right. Choose a different destination or make a copy first if you need to preserve the original text or protect neighboring data.

Why did Excel remove the first zero from my code?
Excel may have interpreted the split value as a number. Set that output column to Text in the wizard before finishing, especially for postal codes and identifiers where leading zeros are significant.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.