How to combine two columns in Excel

How to combine two columns in Excel is a question with four answers: the & operator, CONCAT, TEXTJOIN, and Flash Fill. Each reads both cells and returns one string. Unlike retyping, the result updates when the source changes — and stays a formula until you flatten it. This post covers which versions support each method and the pitfalls that appear after the join.

The ampersand method: works in every version

The & operator is the shortest way to join two cells, and it works in every version of Excel ever shipped.

In an empty cell next to your data, type =A2&" "&B2 and press Enter. Read it left to right: take cell A2, glue on a space, glue on cell B2. Then drag the fill handle — the small square at the bottom-right of the selected cell — down the rest of the column.

The " " in the middle is doing real work. Without it, =A2&B2 produces JaneSmith rather than Jane Smith. Any text you put inside quotation marks gets inserted literally, so =A2&", "&B2 gives you Smith, Jane and =A2&" - "&B2 gives you a hyphen-separated pair. Microsoft's own documentation calls the ampersand quicker and simpler than the CONCATENATE function for building strings this way [1].

The limitation is that & has no idea what a range is. Joining eight columns means typing eight cell references and seven separators, and the formula becomes hard to read and easy to break.

CONCAT and TEXTJOIN: what changed

Excel ships three named functions for this job, and two of them are worth your time.

CONCATENATE is the old one. Microsoft states plainly that in Excel 2016, Excel Mobile, and Excel for the web it has been replaced by CONCAT, and that it survives only for compatibility with earlier versions [1]. Do not start new work with it.

CONCAT is the replacement, and its advantage is ranges. =CONCAT(B2:C8) joins every cell in that block without listing them individually [2]. It reaches back to Excel 2016, 2019, 2021, 2024, and Microsoft 365, on both Windows and Mac.

TEXTJOIN is the most capable of the three. Its signature is TEXTJOIN(delimiter, ignore_empty, text1, …), which means the separator is an argument rather than something you thread manually between every value [3]. Set ignore_empty to TRUE and it skips blank cells instead of leaving a run of stray commas where a middle name should have been. That single argument is why TEXTJOIN is the right pick for address columns, where blanks are normal.

The catch: TEXTJOIN is available in Microsoft 365, 2024, 2021, and 2019 — but not Excel 2016 [3]. CONCAT is supported one version further back [2]. If you do not control which version opens the file, CONCAT is the safer bet.

Both CONCAT and TEXTJOIN return a #VALUE! error if the result runs past Excel's 32,767-character cell limit [2][3].

Combine first and last name in Excel

Merging a first-name and last-name column is the most common version of this task, and it has an official recipe.

For Jane Smith from A2 and B2, use =A2&" "&B2. For the Smith, Jane ordering that most CRMs and mail-merge templates expect, flip the references and change the separator: =B2&", "&A2. The CONCAT equivalent is =CONCAT(A2," ",B2).

Watch for a trailing space in the first-name column — it is close to invisible on screen but produces Jane Smith with a double space after the join. Wrapping each reference in TRIM handles it: =TRIM(A2)&" "&TRIM(B2). TRIM removes all spaces except single spaces between words [7], though it only catches the standard ASCII space. Text copied from a web page often carries non-breaking spaces that TRIM leaves behind, which is why a column can look clean and still fail a match.

Going the other direction — one full-name column that needs splitting into two — is a different job with its own traps, covered in our guide to separating names in Excel.

Flash Fill combines without a formula

If you would rather not write a formula at all, Excel can infer the pattern from an example.

Type the finished result you want in the first cell of an empty column — Jane Smith, typed by hand — then press Ctrl+E, or go to Data > Flash Fill. Excel reads the pattern and fills the rest of the column. Microsoft documents this explicitly for combining first and last names from two columns, as well as for the reverse split [5].

Flash Fill has one genuine advantage over every formula method: it writes static text, not formulas, so there is nothing to flatten afterwards. Its disadvantage is that it is a one-time action. Change a source cell and the Flash Fill result will not update. It also guesses, and on inconsistent data it guesses wrong — check the output before trusting it.

The four things that go wrong

Every method above shares the same set of failure modes, and all four surface after the join rather than during it.

1. The missing separator. =A2&B2 runs the values together. Microsoft's guidance is to add double quotation marks with a space between them as their own argument [1].
2. #NAME? errors. This almost always means a text argument is missing its quotation marks [1]. It can also mean you used TEXTJOIN on Excel 2016, where the function does not exist [3].
3. Numbers and dates losing their format. Microsoft is candid about this one: when you combine text with formatted numbers like dates, times, or currency, Excel does not know how you want them displayed, so it drops the number formatting [4]. A date joins as its serial number. The fix is to state the format yourself with the TEXT function — =A2&" "&TEXT(B2,"mm/dd/yy").
4. Formulas that break on export. This is the one that bites hardest. Your combined column is not text; it is a set of formulas pointing at the source columns. Delete those columns and the whole thing collapses into reference errors. Before the file goes anywhere, copy the column and use Paste Options > Values Only [6]. Microsoft notes that this permanently removes the formulas, so work on a copy.

That fourth pitfall is really a warning about what a spreadsheet formula is for. It is a live calculation inside one workbook, and the moment the data has to travel — to a CSV, to an importer, to somebody else — it has to stop being live.

When a formula is the wrong tool

If the reason you are combining columns is that something downstream demands a single field, the formula route adds a step you do not need.

The sequence in that case is: write the formula, fill the column, flatten it with Paste Values, delete the source columns, then export. Four operations, and forgetting the third quietly corrupts the file. A dedicated join columns tool collapses that into one — pick the two columns and a separator, and the output is static values from the start, with no formulas to flatten and no version question about which function your copy of Excel supports. It runs in the browser, so the file never leaves your device, which matters when the columns you are merging are names and contact details.

The same logic applies to the rest of the pre-import checklist. If the merged column is heading into a CRM, it is worth removing duplicate rows and standardising the fields the importer validates before you convert anything — our walkthrough on cleaning a CSV before a CRM import covers the order that causes the least rework. And if you need the finished file back as a workbook, you can convert it to Excel at the end.

Use a formula when the combined column has to stay live inside a working spreadsheet. Use Flash Fill for a quick one-off. Use a transform tool when the file's destination is somewhere other than Excel — which, for most people combining columns, is exactly the case.