ThisCalculator

Everyday

How to Combine Cells in Excel: 4 Easy Methods

Updated · Standard formula, shown below

This guide shows you how to merge the contents of two or more cells into a single cell in Microsoft Excel, using formulas, the Flash Fill feature, and the built-in Merge tool. You’ll learn which method is best for your data, how to add separators like spaces or commas, and how to avoid common pitfalls that can delete data or break formulas. No prior Excel expertise is needed—just a spreadsheet with data you want to combine.

What You’ll Need

  • Microsoft Excel (2016, 2019, 2021, or Microsoft 365) or Google Sheets (the steps are nearly identical).
  • A worksheet with at least two cells containing text, numbers, or dates that you want to combine.
  • Basic familiarity with selecting cells and typing in the formula bar.

Step-by-Step

Choose one of the following methods based on your needs:

Method 1: Using the Ampersand (&) Operator

  1. Click the cell where you want the combined result to appear.
  2. Type an equals sign (=) to start the formula.
  3. Click the first cell you want to combine, or type its reference (e.g., A2).
  4. Type an ampersand (&) followed by a space in double quotes (” “) to add a space between the values. For example: =A2&" "
  5. Type another ampersand and click the second cell (e.g., =A2&" "&B2).
  6. Press Enter. The combined text appears in the result cell.
  7. To apply to multiple rows, drag the fill handle (small square at the bottom-right of the cell) down.

Method 2: Using the CONCATENATE Function

  1. Select the cell for the result.
  2. Type =CONCATENATE(.
  3. Click the first cell, type a comma, then type a space in double quotes (” “) if you want a separator, then a comma, then click the second cell. Example: =CONCATENATE(A2," ",B2).
  4. Close the parenthesis and press Enter.
  5. Drag the fill handle down to copy the formula to other rows.

Method 3: Using TEXTJOIN (Excel 2019 and Microsoft 365)

  1. Click the result cell.
  2. Type =TEXTJOIN(" ", TRUE, A2:B2) where the first argument is the delimiter (a space in quotes), the second is TRUE to ignore empty cells, and the third is the range to combine.
  3. Press Enter. This joins all cells in the range with the delimiter.
  4. Drag down to fill other rows.

Method 4: Using Flash Fill (for simple patterns)

  1. Type the combined value manually in the first result cell (e.g., type “John Smith” if A2 has John and B2 has Smith).
  2. Select the cell and the cells below it where you want the results.
  3. Go to the Data tab and click Flash Fill (or press Ctrl+E on Windows, Cmd+E on Mac).
  4. Excel detects the pattern and fills the rest. If it doesn’t, type the second example manually and repeat.

Common Mistakes

  • Using Merge & Center without keeping data: The Merge & Center tool (Home tab) keeps only the upper-left value and deletes the rest. If you need to keep all data, use a formula instead.
  • Forgetting the separator: Without a space or comma, values run together (e.g., “JohnSmith”). Always include a delimiter if needed.
  • Not using absolute references when needed: If you copy a formula with relative references, it adjusts to the new row—usually fine, but if you want to keep a fixed cell, use $ (e.g., $A$1).
  • Applying formulas to numbers incorrectly: When combining numbers, the result becomes text. If you need to perform math later, convert back to numbers or use the VALUE function.

Tips

  • Use & for quick, short formulas; use TEXTJOIN for ranges with many cells.
  • To add a line break between combined values, use =A2&CHAR(10)&B2 and enable Wrap Text on the result cell.
  • To combine dates and keep the date format, use =TEXT(A2,"mm/dd/yyyy")&" "&B2.
  • If you want to remove the original columns after combining, copy the result column and paste as values (right-click > Paste Special > Values) before deleting the originals.
  • In Google Sheets, the same formulas work; you can also use the JOIN function.

Troubleshooting

Problem: The formula shows as text (e.g., =A2&B2) instead of a value.
Fix: The cell is formatted as Text. Select the cell, go to the Home tab, change the format to General, then press F2 and Enter to recalculate.

Problem: Flash Fill doesn’t work.
Fix: Make sure the column next to the data is empty or has consistent formatting. Type the second example manually if the pattern isn’t obvious, or use a formula instead.

Problem: The merged result is a number that looks like a date (e.g., 45231).
Fix: This happens when combining date cells. Use the TEXT function to format the date as text before combining.

Problem: When using Merge & Center, a warning says it will keep only the upper-left value.
Fix: That’s expected. If you need all data, undo (Ctrl+Z) and use a formula or CONCATENATE instead.

Frequently Asked Questions

Q: How do I combine cells with a comma and space?
A: Use =A2&", "&B2 or =CONCATENATE(A2,", ",B2).

Q: Can I combine cells from different worksheets?
A: Yes, reference the sheet name: =Sheet2!A2&" "&Sheet1!B2.

Q: How do I combine a column of cells into one cell?
A: Use TEXTJOIN: =TEXTJOIN(" ", TRUE, A1:A100). In older Excel, you’d need a complex formula or a macro.

Q: What’s the difference between CONCATENATE and CONCAT?
A: CONCAT is the newer version that accepts a range (e.g., CONCAT(A1:B5)), while CONCATENATE only accepts individual cells.

Q: Why does my combined number show as text?
A: Because concatenation always returns text. To convert back to a number, wrap the result with VALUE() or multiply by 1.

Q: Can I combine cells without losing formatting (e.g., bold)?
A: No, formulas only carry the text, not formatting. You can copy the format from the original cells to the result cell manually.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.