Split Text Into Columns by Delimiter

Splitting text into columns, and what Excel does differently

Excel has Text to Columns under the Data tab, and it works. What it does not do is show you the result before committing to it, and it will not tell you when a row came out with the wrong number of fields. Both of those are how a split quietly goes wrong: one value contained the delimiter, one row shifted by a column, and the error is discovered three steps later when a total does not add up.

This page splits as you type and shows the grid. If some rows produce fewer fields than others, it says so, which is nearly always the symptom of an unquoted value containing the delimiter. Fixing that in the source and re-pasting takes seconds; finding it after the data is in a spreadsheet takes considerably longer.

Choosing what to split on

Comma is the default and the most common. Tab is what you get when text is copied out of a table in a web page, a Word document or another spreadsheet — that is the one to pick when a paste has arrived as a single column, because tabs are invisible and easy to overlook. Semicolon is standard in regional exports where the comma is a decimal separator. Pipe turns up in log files and database dumps, because it almost never occurs inside a value.

Any whitespace splits on runs of spaces or tabs, which is what you want for output from a terminal or a fixed report where the columns are aligned by padding. Fixed width is for the older case still: mainframe extracts, some banking formats and legacy reports where each field occupies an exact number of characters with no separator at all. Give the widths in order and each line is cut at those positions.

Quoted values are handled

A value that contains the delimiter has to be quoted, and the parser here respects that — including doubled quotes escaping a literal quote inside a field. This is the single most common reason a naive split produces a row one column too wide, and it is worth checking the preview for exactly that before exporting.

Separating text from numbers

A related request is splitting a mixed value such as ABC123 into its letters and its digits. That is not a delimiter problem, since there is no separator to split on — it needs a pattern. Where the boundary is consistent, fixed-width splitting handles it directly. Where it is not, the practical route is to do it in the spreadsheet afterwards with a formula, since only you know what the rule should be.

This page versus text to CSV

The two overlap and are aimed at different situations. This one is paste-and-transform: you have text in your clipboard, you want to see it as columns and get a file out. Text to CSV is file-in, file-out: you have a .txt on disk, you want a clean .csv on disk, with header handling and consistent quoting. Use whichever matches where your data is now. Both run in your browser, and for a spreadsheet with formatting rather than raw columns, text to Excel is the fuller tool.

Next steps