How to Unmerge Columns in Excel

What this does

Combining columns takes the value from each row of two (or more) neighbouring columns and writes them into a single new column, row by row down the whole table — first name beside last name becomes one full-name column. The ampersand (&) joins the two cells of a row with whatever literal text you put in quotes between them, and you copy that one formula down so every row is combined at once. TEXTJOIN does the same across a wider band of columns, taking the separator a single time. This is a per-row column operation, quite different from joining a couple of stray cells or visually merging cells into one box. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. For “unmerge columns in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected columns change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a layout choice that keeps the sheet readable and sortable useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Column A holds first names and column B holds last names, with "Ada" in A2 and "Lovelace" in B2. In C2 type =A2&" "&B2 and press Enter: C2 reads "Ada Lovelace", the " " supplying the space between them. Double-click the fill handle and the formula copies down so every row of column C combines its A and B. For three columns at once, =TEXTJOIN(" ",TRUE,A2:C2) joins A2, B2 and C2 with single spaces. No formula at all? Type the first combined result by hand, then press Ctrl+E (Flash Fill) and Excel fills the rest from the pattern. Combining columns is how you fold scattered fields into the single column a mail merge, import or lookup expects — first + last into full name, street + city into one address line. It is the everyday inverse of splitting one column into several. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “unmerge columns in excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Reaching for Merge & Center — that visually fuses cells and keeps only the top-left value, it does not combine the two columns row by row.
• Forgetting the separator, so "Ada" and "Lovelace" run together as "AdaLovelace".
• Deleting the source columns A and B while the new column still holds live =A2&B2 formulas — copy the result and Paste Special ▸ Values first.
• Combining a number or date directly and losing its formatting — wrap it in TEXT() to keep it readable.

Frequently asked questions