Text to columns

How to split text into columns without Excel's wizard

1. Load the data. Drop a .xlsx or .csv, or paste one column with one value per line. Nothing is uploaded.
2. Pick the column and the delimiter. The column holding commas, semicolons or spaces is pre-selected. The delimiter can be a character, a whole word, or any run of whitespace.
3. Choose columns or rows. New columns are inserted in place of the old one; new rows repeat the other columns for every part.
4. Download. CSV or .xlsx, with the parts kept as text — leading zeros and long numbers are not reinterpreted.

What the Text to Columns wizard does to your sheet

Data → Text to Columns is the answer every tutorial gives, and it works for the simple case. It has four habits that make people search for something else, all of them in Microsoft's own walkthrough once you read it closely:

• It writes into the columns to its right. Splitting column B into three parts fills B, C and D. If C and D held data, Excel asks once whether to replace it, and the dialog is easy to click through.
• The "Other" delimiter is one character. You cannot split on and , - or ->; those questions fill the Excel forums.
• Each new column is "General" by default, so 00101 becomes 101 and a 16-digit reference turns into scientific notation unless you set every column to Text in step three.
• It only makes columns. One cell holding three e-mail addresses cannot become three rows.

This page inserts the new columns in place of the old one and shifts the rest right, accepts any string as the delimiter, keeps every part as text, and can put the parts on separate rows.

TEXTSPLIT, and who has it

The TEXTSPLIT function takes a multi-character delimiter and can split into rows, which covers two of the four problems. Microsoft lists it for Excel for Microsoft 365 and Excel 2024 only, so a workbook opened in Excel 2021, 2019 or LibreOffice shows #NAME? where the parts should be. It also spills a formula rather than leaving values, which is one more step before the sheet can be sent on.

Quotes, doubled delimiters and the leftover part

• Quoted parts stay whole. In "Smith, Jr.", John the comma inside the quotes is not a delimiter, so the result is two parts.
• Doubled delimiters — a,,b from a missing middle name — give an empty part by default, which keeps the columns aligned. Tick Treat doubled delimiters as one to drop it.
• At most N parts keeps the remainder together: an address split into two parts on the first comma gives the street, then everything after it unchanged.
• Spaces around each part are trimmed, so Byron, Ada gives Ada, not ␣Ada.

Columns or rows

New columns is the wizard's behavior: the header becomes name 1, name 2 … and short cells are padded with blanks. New rows repeats every other column for each part, which is the shape a pivot table, a lookup or a mail merge needs when one cell holds a list.

When the same file has to be split every month

If the export arrives every period with the same packed column, the split is a step in a job, not a chore. TableDI 2 is a desktop app for file work you redo every period: it keeps your sources, rules and delivery as a job, so next month you drop in the new files and run it again. Everything runs on your own machine.