Excel Text to Columns: Delimited, Fixed Width and Pitfalls

The 7 steps of the Delimited wizard

Microsoft's wizard page lists these steps for Excel for Microsoft 365, 2024, 2021, 2019 and 2016.

• Select the cell or column that holds the text.
• Open the Data tab and choose Text to Columns.
• Choose Delimited and go to the next screen.
• Tick the delimiters. Microsoft's example uses Comma and Space, and the Data preview window shows the result.
• Go to the next screen.
• Choose the destination cell where the split data should appear.
• Finish the wizard.

It splits one column and writes to the right

Microsoft's page on distributing cell contents says the selected range can include any number of rows but no more than 1 column. The pieces go into the adjacent columns on the right.

The same page warns you to keep enough blank columns there, to prevent data in any adjacent columns from being overwritten. Insert empty columns first if the sheet is full. Choosing a different destination cell in step 6 is another way to keep the original intact.

Delimited or fixed width: which one fits your data

Delimited fits values separated by characters such as tabs, colons, semicolons or spaces. Fixed width fits data where every item in a column has the same length, such as an old report export with a 5-character code at the start of each line.

In fixed width mode you click the preview to set a column break, double-click a break to remove it and drag it to move it. The delimiter options do not apply there. These details are from Microsoft's Text Import Wizard page, which shares the Delimited and Fixed width choice.

A worked example: one cell, three pieces, one quoted comma

These are sample values. Cell A2 holds: Hauser,"Dallas, Texas",02134. The text has 3 fields but 3 commas.

With Comma ticked and no text qualifier, you get 4 cells: Hauser, "Dallas, Texas" and 02134, with the quote marks left on the two middle pieces. Microsoft's page shows the same effect for "Dallas, Texas": with a comma delimiter and no qualifier, or with an apostrophe as the qualifier, it splits into 2 cells.

Set the text qualifier to the quotation mark and the 3 cells are Hauser, Dallas, Texas and 02134. When the delimiter sits between qualifiers, Excel removes them.

Now the last field. Excel removes leading zeros from numbers, so a numeric column left as General can lose the 0 and show 2134. Microsoft's step 3 offers the Text format for all-number columns you want kept as text, so choose Text for that column to keep 02134 as typed. Check the Data preview before you finish.

Column data formats: General, Text, Date, Do not import

Step 3 offers 4 formats for the selected column. General suits currency-style or mixed numeric values. Text keeps all-number columns as text. Date needs a date order, such as YMD. Do not import column (skip) leaves the column out.

Excel falls back to General when a conversion could give unintended results. Microsoft names two cases: columns that mix letters and numbers, and dates whose order does not match the selected one, for example YMD data set to MDY.

Numbers over 15 digits lose their end

Excel has a maximum precision of 15 significant digits. A 16-digit number such as 1234567890123456 keeps 1234567890123450 after conversion, because digits past the 15th are rounded down to zero. Microsoft names credit card numbers as the usual case and says to use a text format for codes of 16 or more digits.

Set the format before splitting. Select the destination range, press Ctrl+1 and choose Text. This does not change numbers already entered, so the order matters.

Consecutive delimiters and the "Treat as one" box

If two spaces sit between words, the wizard creates an empty cell for the gap unless you tick Treat consecutive delimiters as one. Microsoft's description says to use this box when fields are separated by a multi-character delimiter or multiple custom delimiters. The Other box takes a custom character.

Names with 3 or 4 words show the limit of a space split

Splitting "Paula Barreto de Mattos" on a space gives 4 cells, not a first name and a last name. Microsoft's formula page handles this case with =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)), which returns Barreto de Mattos as the last name. For names like this, formulas with LEFT, MID, RIGHT, SEARCH and LEN give more control than the wizard.

TEXTSPLIT is the formula version

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) is available in Microsoft 365 and Excel 2024. Unlike the wizard, it updates when the source changes. Setting ignore_empty to TRUE ignores consecutive delimiters; the default FALSE makes an empty cell. The match_mode default 0 is case-sensitive, and the pad_with default is #N/A. For 2 delimiters you need an array constant, such as {",","."}.

If you are stuck on a dialog and are not sure which choice it wants, Crade can read the screen and explain it.

Who the wizard suits

The wizard suits a one-time split of a pasted column, in any version from Excel 2016. TEXTSPLIT suits live data in Excel 2024 or Microsoft 365. Excel for the web has Data > Split Text to Columns, where you select delimiters and then Apply.