Split & Merge Columns in Excel: Reshape Your Table from One Column to Many and Back

Split & Merge Columns

Sometimes one column hides several pieces of information, and sometimes several columns belong together. This tutorial covers splitting one column into many and merging many columns into one — the two operations that reshape your table.

Table of Contents

• Split Columns
• Merge Columns

Split Columns

What it is. Breaking one column into two or more, based on a delimiter (comma, space) or fixed width.

Why it is useful. A "Full Name" column can't be sorted by last name. An address stuffed in one cell can't be used for a mail merge. Splitting unlocks the pieces.

Technique 1 — Text to Columns.

1. Select the column.
2. Data → Text to Columns → Delimited (or Fixed width).
3. Pick the delimiter (space, comma, etc.) → Finish.

Example. A2 = John Smith → becomes two cells: John | Smith.

Explanation. Excel cuts the text at each space into separate columns.

Technique 2 — Formulas. =LEFT(A2, FIND(" ", A2)-1) → first name =RIGHT(A2, LEN(A2)-FIND(" ", A2)) → last name

Explanation. FIND locates the first space; LEFT/RIGHT take the text on each side of it. This is the way when you need the result to update automatically.

Best practice. Use Flash Fill (Ctrl + E) when the split pattern is irregular — like pulling the city out of varying addresses — since it learns from your examples.

Common mistake. Running Text to Columns without a blank column next to the data. It overwrites whatever sits to the right. Insert empty columns first.

Merge Columns

What it is. Combining two or more columns into one — first and last names into a full name, street and city into an address.

Why it is useful. Some tools, reports, and mail merges expect a single field. Merging also creates unique keys for matching.

Syntax.

• =A2 & " " & B2 — the & operator glues text together.
• =TEXTJOIN(", ", TRUE, A2:C2) — joins a whole range with a separator and skips empty cells.
• =CONCATENATE(A2, " ", B2) — the older, clunkier way.

Example. A2 = John, B2 = Smith. =A2 & " " & B2 → John Smith =TEXTJOIN(", ", TRUE, A2:C2) with C2 empty → John, Smith

Explanation. The & operator is simple and fast for a few cells; TEXTJOIN is cleaner for many columns and ignores blanks automatically.

Best practice. When building a unique key (like a person or order ID), add a separator that can't appear in the data — =A2 & "|" & B2 — so "John" + "Smith" and "John S" + "mith" don't collide.

Common mistake. Merging numbers with & turns them into text: 1 & 0 is "10" (text), not the number 10. Convert back with =VALUE(...) or multiply by 1 if you need to do math.

Let AI do it. Copilot: "Combine first and last name into one Full Name column."