Excel Text to Columns: Choosing a Number Delimiter

Excel’s Text to Columns has no special “number delimiter”: choose the character—such as a comma, tab, space, semicolon, or another symbol—that separates the values in your cells. In Microsoft 365 for Windows, select the source column, go to Data > Text to Columns, choose Delimited, select the matching separator, and check the preview before finishing.

What a delimiter means in Excel

A delimiter is a character that marks where one piece of text ends and the next begins. It is not a setting for how Excel recognizes numbers, and there is no standard delimiter that works for every dataset. The right choice depends on the actual contents of the cells.

For example, the text Rina,Seattle,42 uses commas between three fields. The text Rina|Seattle|42 uses vertical bars. If the fields are separated by tab characters, choose Tab instead. After splitting, Excel places the fields in adjacent columns on the same row.

Text to Columns can also split data by position rather than by a character. That option is called Fixed width. It is useful when fields line up at consistent character positions, even if there is no visible separator.

How to split a column with a delimiter

Before starting, keep a copy of the original data or make sure you can undo the change. Text to Columns writes results into cells beside the source, so existing content in those cells may be replaced. The wizard’s preview helps you catch a wrong separator before applying the split.

Select the column containing the combined text. If you select a single cell in the column, Excel may use the surrounding data to determine the range.
Choose Data > Text to Columns on the ribbon.
In the wizard, choose Delimited and select Next. Choose Fixed width instead only if the fields are separated by consistent positions rather than characters.
Select the delimiter that appears between your values: Tab, Semicolon, Comma, Space, or Other. For Other, type the separator character in its box.
Review the preview. If the vertical lines appear in the wrong places, change the delimiter or clear the selection and check again.
Select Next to set column formats or a destination if needed, then choose Finish.

For example, if A2 contains Rina|Seattle|42, selecting Other and entering | should preview three fields. The original cell is in the first output column, and the other fields are placed to its right. If those neighboring cells contain information you need, choose an empty destination in the wizard or move the source data first.

How to choose the right delimiter

Look closely at several rows, not just the first one. A delimiter should occur between fields consistently, but it may also appear inside a field. A comma can separate fields in one dataset and be part of an address, description, or number in another. The preview is the quickest way to see whether Excel is splitting at every occurrence of the chosen character.

Choose Tab when columns were copied from a table or the data visibly has tab-separated fields.
Choose Comma for ordinary comma-separated text when commas divide the fields.
Choose Semicolon when semicolons separate the values.
Choose Space when spaces are the intended boundaries, such as between a first and last name only if splitting those names is what you want.
Choose Other for symbols such as a pipe, slash, or colon when that symbol is the actual boundary.

Do not select multiple delimiters just because more than one character appears in the data. Selecting both comma and space, for instance, can split a phrase at spaces as well as commas. That may turn a two-field row into several columns. Choose only the separators that represent field boundaries.

Numbers, decimal marks, and leading zeros

A number is not itself a delimiter. If a cell contains Item-00427-18.50, the hyphens are delimiters; 00427 and 18.50 are values between them. Excel may interpret those values as numbers when the output column’s format is General. That can change how they display or how they behave in calculations.

Be especially careful with codes that need leading zeros, including product IDs, postal codes, and account references. If Excel converts 00427 to a number, it will usually display as 427. In the wizard’s final step, select the relevant output column in the preview and choose Text as its column data format to preserve the characters as entered. Text is also appropriate when a value looks numeric but is an identifier rather than a quantity.

Decimal and thousands separators depend on how the numbers are written and on Excel’s regional settings. A value such as 1,234.56 uses a comma for thousands and a period for decimals in one common format. If you choose comma as the field delimiter, Excel may split that number at the thousands comma unless the value is protected as a quoted field or imported with the appropriate settings. In the wizard’s final step, the Advanced option lets you specify decimal and thousands separators for number interpretation. Check the preview and the resulting cells rather than assuming every computer uses the same conventions.

Examples of common delimiter choices

Suppose each row contains Lee;Sales;West. Choose Semicolon to put the name, department, and region into separate columns. Choosing Comma would not split that row, because no commas mark the boundaries. If the same data arrives as Lee,Sales,West, Comma is the appropriate choice instead.

Consider a list of full names such as Sam Lee. Choosing Space divides the name into two columns, but it can also create extra columns for middle names or compound surnames. If the goal is to separate first and last names accurately across varied names, a simple space delimiter may not be sufficient; inspect the data and decide how to handle exceptions.

A pipe-separated record such as North|00427|18.50 illustrates why the delimiter and output format are separate decisions. Choose the pipe to create three fields. Then set the middle field to Text if its leading zeros must remain, and check how the final field is interpreted as a decimal value.

Common problems and how to avoid them

Everything stays in one column: The selected delimiter may not match the character in the source. Check for tabs, semicolons, or an unusual symbol, and test the choice in the preview.
One field becomes several columns: The chosen character may also occur inside values, or you may have selected more than one delimiter. Use a more specific separator or examine the text qualifier setting when fields are quoted.
Leading zeros disappear: Excel interpreted a field as a number. Set that output column to Text before finishing, or restore the source and repeat the split with the correct format.
Dates or decimals change: Excel may interpret text according to regional settings or General formatting. Review the data format and the Advanced number separators setting before applying the split.
Existing cells get replaced: Text to Columns outputs into adjacent cells. Clear that area, move the source, or specify an empty destination before you finish.

The Text qualifier setting is useful when a field is enclosed in quotation marks and contains a delimiter that should not split it. The default qualifier is commonly a double quote. Check the preview to confirm that quoted text stays together. For complex files with quoting rules, importing through Data > From Text/CSV is often a better fit than manually splitting an already-loaded column.

Delimited versus Fixed width

Delimited and Fixed width solve different problems. Delimited is the right choice when a visible character marks the boundary, such as a comma between fields. Fixed width is designed for records where each field occupies a consistent position, such as text in which the first eight characters are an ID and the next section is a date.

In Fixed width mode, Excel shows a preview where you can add, move, or remove break lines to define the columns. It does not search for a separator character. If the spacing varies from row to row, fixed breaks can split records inconsistently; use a delimiter or another import method when the boundaries are not positionally stable.

Alternatives for repeatable splitting

Text to Columns is convenient for a one-time cleanup. If new rows arrive regularly, a repeatable import or formula-based approach may be easier to maintain. Microsoft 365 and Excel 2024 include the TEXTSPLIT function, which can divide text around a specified delimiter in a worksheet. It is a formula option rather than a wizard setting, so it may suit data that needs to update when the source changes.

For files saved as CSV or another structured text format, Data > From Text/CSV provides import controls before the data is loaded. This can be helpful when you need to choose a delimiter, review data types, or handle a file containing quoted fields. For a quick split of an existing column, Text to Columns remains a straightforward choice.

In Excel for Mac, the Text to Columns command is also available from the Data tab, although the wizard’s appearance can vary by version. The same core rule applies: select the delimiter that separates fields and verify the preview. If a command or option is not shown in your build, use the desktop Excel version that provides the wizard or import the source file through its available text-import controls.

Frequently Asked Questions

What delimiter should I use for numbers in Excel Text to Columns?
Use the character between the values, not a special number delimiter. For example, choose a comma for comma-separated fields or a pipe for pipe-separated fields. Set the output format separately if Excel should preserve the values as text.

How do I split numbers separated by commas?
Select the source column, open Data > Text to Columns, choose Delimited, and select Comma. Check that commas are field boundaries, not thousands separators inside a number, before choosing Finish.

How can I keep leading zeros when using Text to Columns?
In the wizard’s final step, select the output column containing codes and choose Text for its column data format. This prevents Excel from treating an identifier such as 00427 as the number 427.

Why does Text to Columns split my data incorrectly?
The selected character may occur within a field, or another selected delimiter may be splitting the text too. Review the preview, select only the intended separator, and check the Text qualifier setting for quoted values.

Can I split text at a fixed number of characters?
Yes. Choose Fixed width in the wizard instead of Delimited, then adjust the break lines in the preview. This works best when each field starts at the same character position in every row.

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.