Split a CSV Column

Splitting a column turns one field into several: a full name into first and last, a tag list into one column per tag, a fixed-width record into its fields. Choose a delimiter, a pattern or a set of character positions. The number of new columns is worked out from the whole file, not from the first row. It all runs in this browser tab.

Going the other way? Merge several columns into one.

Three ways to cut, because there are three problems

Splitting a column sounds like one job. It is three, and a tool that only offers the first one sends you to a text editor for the other two.

A delimiter covers most of it. A space between a first name and a last name, a semicolon between tags, a slash between a category and a subcategory. Type the character into the box. Escape sequences are understood, so \t means a tab rather than a backslash followed by a t.
A pattern covers the case where the separator varies. Real exports are full of red; green ,blue, where a fixed delimiter leaves you with leading spaces on some parts and not others. The default pattern, \s*[,;]\s*, reads as "a comma or a semicolon with any amount of space around it" and handles the lot.
Character positions cover fixed-width records, which have no separator at all. Mainframe extracts, banking files and old report dumps put field two at column 17 and field three at column 27 whatever field one contained. Type 16, 26 and the cuts happen there.

Worked example: a fixed-width record

The example button loads this, which is what a report dump looks like once it has been saved with a .csv extension by somebody in a hurry:

record
ADA  LOVELACE   LONDON    1815
GRACE HOPPER    NEW YORK  1906
ALAN TURING     LONDON    1912
KATHERINE JOHNSNWHITE SPHR1918

One column, four rows, and no separator you can trust: the city on row 2 contains a space, so splitting on whitespace would give NEW and YORK as two fields. Switch Split on to character positions, leave the cuts at 16, 26, and the result is:

record,record_1,record_2,record_3
ADA  LOVELACE   LONDON    1815,ADA  LOVELACE,LONDON,1815
GRACE HOPPER    NEW YORK  1906,GRACE HOPPER,NEW YORK,1906
ALAN TURING     LONDON    1912,ALAN TURING,LONDON,1912
KATHERINE JOHNSNWHITE SPHR1918,KATHERINE JOHNSN,WHITE SPHR,1918

NEW YORK survives as one value. Row 4 shows what fixed-width files do when a name is too long: the record is truncated in the source, and the tool reproduces that faithfully rather than guessing at a repair. Each part is trimmed by default, which is why the trailing padding is gone but the double space inside ADA LOVELACE is not; internal spacing is the whitespace tool's job.

Why the part count comes from the whole file

Here is a bug worth knowing about, because it is silent and it is common. A tool reads the first row, sees two parts, creates two columns, and then processes the rest of the file into them. Row 400, which has three parts, quietly loses its third. Nothing on screen says so. The file downloads. The problem surfaces a week later when somebody notices a category is missing.

This page reads the entire column first and sizes the output to the widest row it finds. A file where most rows split into two and one row splits into five gets five columns, and the rows that only had two get blank cells in columns three, four and five rather than being shifted along. The stats strip counts the rows that came up short:

3 rows in · split "name" · on " " · 3 new columns · parts trimmed · original kept

1 row produced fewer than 3 parts, so the missing cells are blank
rather than shifted left.

If a column that should split cleanly does not, that count is the first thing to look at. It usually means two different formats are mixed together in one column, which is worth knowing before the file goes anywhere.

Capping the parts, and where the leftovers go

Max parts exists for the column that is mostly two fields and occasionally forty. Addresses do this. So do free-text notes that somebody has been using as a tag list since 2019.

With a cap of 2 on red,green,blue,white, the two settings of Past the cap give you:

Last part keeps it, the default: red and green,blue,white. The remainder is rejoined with the delimiter it was split on, so nothing is lost and the last column holds the rest verbatim. This is what you want for "first word, then everything else".
Drop it: red and nothing else. Use it when the tail genuinely is noise, and know that it is being discarded.

Leave the cap empty and there is no limit; the output is as wide as the widest row needs.

Things the pattern mode will do that surprise people

A capturing group becomes a part. That is how splitting on a pattern works in JavaScript: whatever a bracketed group matched is kept alongside the pieces. Sometimes that is what you want. When it is not, write the group as (?:...), which groups without capturing. The page warns you when your pattern contains a capturing group, because the extra columns are otherwise baffling.
An invalid pattern is an error, not a silent no-op. Type ([ and you get the reason back rather than an unchanged file.
A separator that never appears is reported. If no row contains the delimiter you typed, the result is one part per row and the stats strip says how many rows had nothing to split. That is nearly always a typo or the wrong column.
Positions are one-based character offsets, and they are sorted for you. Typing 26, 16 and 16, 26 mean the same thing. Duplicates are collapsed.
New columns are named after the source. Splitting name gives name_1, name_2 and so on, and if a column called name_1 is already in the file the new one becomes name_1_2 rather than overwriting it.
Quoted fields are handled before any of this. A cell that contains the delimiter inside quotes arrives at the splitter as one value, so "Acme, Inc." in a comma-delimited file is not two cells to begin with.

What this does not try to be

Splitting on a separator is a blunt instrument, and it is the right instrument surprisingly often. When it is not, two other pages on this site are usually the answer.

If what you want is a specific piece out of the middle of a value rather than all the pieces, extracting with a regular expression is the better fit: capture groups become named columns and rows that do not match are counted rather than producing a lopsided table. Pulling the version number out of /api/v2/orders is an extract job, not a split job.

If the file itself is one big column because the delimiter was never detected, this is the wrong page entirely. Set the input delimiter, or use the delimiter changer. Splitting the single column by hand works, and it will lose every quoted field that contains the separator.

Frequently Asked Questions

Can I split on more than one character?

Yes. The delimiter box takes any string, so " | " with spaces around the pipe works as one delimiter. For a separator that varies, switch Split on to a pattern; the default pattern matches a comma or a semicolon with any amount of space around it.

How do I split on a tab?

Type \t in the delimiter box. Backslash escapes for tab, newline and carriage return are understood, which is the only way to enter those characters in a single-line text field.

Why did I get more columns than I expected?

Either one row in the file has more parts than the rest, since the output is sized from the whole column rather than the first row, or your pattern contains a capturing group and what the group matched is being kept as a part of its own. The page warns about the second case.

What happens to rows with fewer parts?

They get blank cells at the end, never shifted values. A row that splits into two in a three-column output has its two values in columns one and two and a blank in column three. The stats strip counts how many rows came up short.

Can I keep the leftovers when I cap the number of parts?

Yes, and that is the default. With Max parts set to 2 and Past the cap on Last part keeps it, red,green,blue,white becomes red and green,blue,white, with the remainder rejoined using the delimiter it was split on. Switch to Drop it and everything after the second part is discarded.

How do I split a fixed-width file that has no delimiter?

Set Split on to character positions and type the offsets to cut at, separated by commas. For a file whose first field is sixteen characters wide and whose second is ten, that is 16, 26. Values are cut at those positions whatever they contain, which is the point: a city called New York stays one field.

Does splitting damage the rest of the file?

No. Only the chosen column is touched, the new columns are inserted next to it, and every other cell is copied through as the string it was, so a leading zero survives. By default the source column is kept as well, so you can check the split before you rely on it.

Is the file uploaded?

No. This page has no upload endpoint. The file is read, split and rewritten by JavaScript in your own tab, and nothing is kept between visits.

One column in, several out

Free, no account, no upload. Pick the column, pick the cut, check the preview, take the CSV.

Back to the splitter