Split & Combine Columns

About splitting and combining columns

These are one tool because they are one job seen from two sides. A full name that should be two columns; two columns that should be one address line. The same person needs both within a minute of each other, and neither is worth a separate page.

Excel calls the first Text to Columns, a three-step wizard that overwrites the columns to its right without warning. The second it calls CONCATENATE, or &, or TEXTJOIN depending on the version.

Here both show you the result as you set them up, and neither touches your original file.

Splitting a column

• Split on any character or string. A space, a comma, a hyphen — type it in. \t means a tab, which is otherwise impossible to type into a box
• At most this many pieces stops a three-word name becoming three columns when you only wanted two
• Keep the remainder in the last piece decides what happens to what is left over. On, Ada Byron Lovelace split into two becomes Ada + Byron Lovelace; off, the surname is dropped. It is on by default, because silently losing data is the worse mistake
• Split on a regular expression for the messy cases — several possible separators, or a split that depends on what surrounds it

The new columns take their name from the original: a column called Name becomes Name 1, Name 2.

Combining columns

• Tick the columns to join — they are joined in the order they appear in the table
• Join with whatever goes between them: a space, a comma and a space, - , or nothing at all
• Name the new column, or leave it blank to get First + Last automatically
• Replace the columns that were joined puts the result where the first one was. Untick it to add the joined column at the end and keep the originals

A blank cell in the middle does not leave a dangling separator. Joining Ada, , Lovelace with a space gives Ada Lovelace, not Ada Lovelace.

How to use it

• Paste your rows or upload a spreadsheet
• Pick split or combine and set it up — the table below updates as you type
• Download as CSV or Excel, or copy it back into your sheet

What people use it for

• Splitting full names into first and last
• Pulling a city and country apart after they were exported into one cell
• Separating a date and time that arrived as one string
• Joining address lines into a single field for a mail merge
• Building a key column by combining two or three others
• Turning Surname, Forename into two proper columns

Good to know

• Works on Windows, macOS, Linux, ChromeOS, and on phones and tablets — it takes pasted text or a file, not a folder
• Nothing is uploaded. Reading, splitting and joining all happen in the page
• When splitting, every row gets the same number of new columns — the widest result decides, and shorter rows are padded, so the table stays rectangular
• An invalid regular expression is reported as invalid rather than quietly matching nothing
• The preview shows the first 200 rows; the download always contains every one

Related tools

• Text to Table — split pasted lines that are not a spreadsheet yet
• Transpose Rows and Columns — flip the table instead
• Find and Replace — clean the values before splitting them
• Column to Comma Separated List — a whole column onto one line