Split a Column into Several Columns
Choose the column and the text to split at, such as a comma, a space or a slash. You can limit the number of parts, so a full name splits into first name and everything else.
Drag a file here or choose one
Worked examples
Surname and first names
Full name Lee, Ann Marie Ray, Bob
Last,First Lee,Ann Marie Ray,Bob
Split at a comma, at most 2 parts, new columns named Last and First.
A date as a number
20240315
2024,03,15
Fixed widths 4, 2, 2.
About Split a Column into Several Columns
Data often arrives with several facts in one cell: a name written as Lee, Ann, a date as 2024-03-15, an address with commas, a code such as NORTH-2024-017. Splitting puts each part in its own column, directly after the original, so the rest of the table is unchanged. It does the job of text to columns in a spreadsheet and works on every row at once.
Choose the column and what to split at: a character or any text, runs of spaces, or fixed widths. A limit on the number of parts keeps the last piece whole, so Lee, Ann Marie split at the first comma gives Lee and Ann Marie. Fixed widths cut a value into pieces of set lengths, which splits 20240315 into 2024, 03 and 15. Rows with fewer parts are filled with blanks, and you can name the new columns.
How to use Split a Column into Several Columns
- Paste your CSV or open a file.
- Choose the column to split and what to split at.
- Set the most parts if you want the last piece kept whole, and name the new columns.
- Decide whether to keep the original column, then download the result.
Common uses
- Separating surname and first name
- Splitting a date written as one number into year, month and day
- Breaking an address into lines
- Pulling the parts out of a code such as REGION-YEAR-NUMBER
Frequently asked questions
What does the limit on parts do?
It stops splitting after that many pieces and keeps the rest together in the last one. With a limit of 2, Lee, Ann Marie becomes Lee and Ann Marie.
How do fixed widths work?
Give the length of each piece, such as 4, 2, 2. The value 20240315 becomes 2024, 03 and 15. Anything left over becomes an extra part.
What happens to rows with fewer parts?
They get blank cells in the columns they do not fill, so every row has the same number of columns.
Can I keep the original column?
Yes, tick Keep the original column. The new columns go straight after it.
Do spaces around parts matter?
By default they are trimmed, so Lee, Ann gives Lee and Ann without a space at the front. Untick the option to keep them exactly.