What merging fields means and when you need it

Merging fields in Excel means taking text or numbers from separate cells and combining them into a single cell. The most common reason to do this is to create a full name from a first name and last name that are stored in different columns, or to build an address by pulling street, city, and state from three separate cells.

Excel gives you two main ways to do this. The first is concatenation, which joins the contents of cells together using a formula. The second is the TEXTJOIN function, which does the same thing but handles spacing and separators more cleanly. Concatenation is older and works in every version of Excel. TEXTJOIN is newer and easier to read, but only works in Excel 2016 and later on Windows, or Excel 2016 and later on Mac.

You should merge fields when you need to display combined information in a report, create mailing labels, or prepare data to send to another program. You should not merge fields if you plan to sort or filter by those individual pieces of information later — once they are combined, Excel cannot separate them again without starting over.

Key Takeaways

  • Concatenation uses the ampersand symbol (&) to join cells, and works in all versions of Excel: =A1&" "&B1 combines the contents of A1 and B1 with a space between them.
  • TEXTJOIN is simpler to read and handles spacing automatically, but only works in Excel 2016 and later: =TEXTJOIN(" ",TRUE,A1,B1) does the same thing with less typing.
  • You can merge fields in a new column without changing the original data, so you keep both the separate cells and the combined result.
  • Once fields are merged into a single cell, you cannot sort or filter by the individual pieces — plan your layout before you combine anything.

Using concatenation with the ampersand symbol

Concatenation is the most straightforward way to merge fields, and it works in every version of Excel. Open your spreadsheet and click on an empty cell where you want the combined result to appear. Type an equals sign to start a formula, then type the cell reference for the first piece of information, followed by an ampersand (&), then the next cell reference.

For example, if your first name is in cell A2 and your last name is in cell B2, type =A2&B2 and press Enter. Excel will combine them, but they will run together with no space. To add a space between them, type =A2&" "&B2 instead. The quotation marks around the space tell Excel to insert a literal space character between the two names.

You can chain as many cells together as you need. If you want to combine first name, middle initial, and last name from cells A2, B2, and C2, type =A2&" "&B2&" "&C2. You can also add punctuation or other text the same way: =A2&", "&B2 will put a comma and space between the contents of A2 and B2.

Once you have entered the formula in one cell, copy it down to all the rows that need it. Click the cell with your formula, then drag the small square in the bottom right corner of the cell down to the last row you want to fill. Excel will automatically adjust the cell references for each row — the formula in row 3 will become =A3&B3, the formula in row 4 will become =A4&B4, and so on.

Using TEXTJOIN for cleaner formulas

TEXTJOIN is a newer function that does the same job as concatenation but with less typing and more flexibility. It is available in Excel 2016 and later on both Windows and Mac. Click on an empty cell and type =TEXTJOIN(" ",TRUE,A2,B2) where the space in quotation marks is your separator, TRUE tells Excel to ignore empty cells, and A2 and B2 are the cells you want to combine.

The advantage of TEXTJOIN is that it handles spacing automatically and reads more like plain English. If one of your cells is empty, TEXTJOIN will skip it without leaving an extra space behind. With concatenation, an empty cell would leave a gap. For example, if you use =TEXTJOIN(" ",TRUE,A2,B2,C2) and B2 is empty, you will get the contents of A2 and C2 with a single space between them. With concatenation, you would get an extra space where B2 should have been.

You can also use TEXTJOIN to combine cells with different separators. If you want to join a city, state, and zip code with commas, type =TEXTJOIN(", ",TRUE,A2,B2,C2). The comma and space will appear between each piece of information.

Keeping your original data intact

When you merge fields, always put the formula in a new column rather than overwriting your original data. This way you keep both the separate cells and the combined result. If you later realize you made a mistake or need to change how the fields are combined, you can edit the formula without losing the original information.

For example, if your first and last names are in columns A and B, put your merged formula in column C. This leaves columns A and B unchanged. If you need to sort or filter by first name or last name later, you still can, because those columns are still separate.

If you have already created formulas in a new column and you want to keep just the results without the formulas, you can convert the formulas to plain text. Select the cells with your formulas, copy them, then right-click and choose Paste Special. Click the Values radio button and click OK. Now the cells contain the combined text, not the formula, so they will not change if you edit the original cells.

Combining data from more than two cells

You can merge as many cells as you need in a single formula. The process is the same whether you are combining two cells or ten. With concatenation, keep adding ampersands and cell references: =A2&" "&B2&" "&C2&" "&D2. With TEXTJOIN, list all the cells you want to combine inside the parentheses: =TEXTJOIN(" ",TRUE,A2,B2,C2,D2).

A common use case is building a full address. If you have street address in A2, city in B2, state in C2, and zip code in D2, you might use =TEXTJOIN(", ",TRUE,A2,B2,C2,D2) to create a single address line. Or you could use line breaks to format it vertically: =A2&CHAR(10)&B2&", "&C2&" "&D2. The CHAR(10) function inserts a line break, so each part of the address appears on its own line within the same cell.

Troubleshooting common problems

If your merged cells show a formula instead of the result, you are probably looking at formula view rather than normal view. Press Ctrl+` (backtick, the key to the left of the 1) on Windows, or Ctrl+` on Mac, to toggle back to normal view. You should see the combined text instead of the formula.

If your merged result shows an error like #NAME?, you probably misspelled a function name or forgot the equals sign at the start of the formula. Check that you typed =TEXTJOIN or that you used the ampersand symbol correctly. If you are using TEXTJOIN and it does not work, your version of Excel may be older than 2016 — switch to concatenation with the ampersand instead.

If your merged cells are showing numbers that look wrong, the original cells may contain numbers formatted as text, or text formatted as numbers. This usually does not cause a visible problem, but if you need to sort or calculate with those cells later, it can cause issues. For now, the merged result should still display correctly.

Frequently Asked Questions

Can I merge fields without creating a formula?

No, you need a formula to combine the contents of separate cells. Excel's Merge Cells feature does something different — it combines the appearance of multiple cells into one larger cell, but it does not combine the data inside them. Use concatenation or TEXTJOIN instead.

What happens if one of the cells I am merging is empty?

With concatenation, an empty cell will leave a gap or extra space in your result. With TEXTJOIN and the TRUE setting, empty cells are skipped automatically. If you want concatenation to skip empty cells too, you need a more complex formula using the IF function, which is beyond the scope of basic merging.

Can I undo a merge and separate the cells again?

If your merged data is still a formula, you can delete the formula and go back to your original cells. If you converted the formula to values using Paste Special, the original separate data is gone and cannot be recovered — you would need to re-enter it or restore from a backup.

Does merging fields work the same way in Google Sheets?

Google Sheets uses the same concatenation method with ampersands, and TEXTJOIN works the same way too. The formulas are identical, so if you learn to merge fields in Excel, you can do it in Google Sheets without any changes.