keepcalculator

06 Numbers and Powers

How to Join Values with the Ampersand in Excel

Card No. 06·48879 · Updated · Standard formula, shown below

Joining values with the ampersand (&) in Excel lets you combine text, numbers, and cell references into a single string. This is useful for creating full names, addresses, or custom messages without writing complex formulas. You only need Excel and the data you want to combine.

What You’ll Need

  • Microsoft Excel (2010 or later; the steps work the same in Google Sheets)
  • Data in cells that you want to combine (text, numbers, dates, etc.)
  • A cell where you want the result to appear

Step-by-Step

  1. Identify the cells to join. For example, if you have first names in column A and last names in column B, note the cell references (A2, B2, etc.).
  2. Select the cell for the result. Click the cell where you want the combined text to appear (e.g., C2).
  3. Type the equals sign. Every formula in Excel starts with =.
  4. Enter the first cell reference. Type A2 (or click on cell A2).
  5. Type the ampersand. Press Shift+7 on most keyboards (the & symbol).
  6. Add any literal text in double quotes. For a space, type " " (quote, space, quote). For a comma and space, type ", ".
  7. Type another ampersand and the next reference. For example, &B2.
  8. Press Enter. The result appears in the selected cell. For A2=”John” and B2=”Doe”, the formula =A2&" "&B2 returns “John Doe”.
  9. Copy the formula down. If you have more rows, drag the fill handle (the small square at the bottom-right of the cell) down to apply the same formula to other rows.

Common Mistakes

  • Forgetting the equals sign. Without it, Excel treats the entry as text, not a formula.
  • Missing quotes around literal text. If you type =A2& &B2 (without quotes around the space), Excel shows an error because it sees an undefined name.
  • Using commas instead of ampersands. Commas are for function arguments, not concatenation. Use &.
  • Not adding spaces. Joining “John” and “Doe” without a space gives “JohnDoe”. Always include a space or other delimiter if needed.
  • Overlooking number formatting. When you join a number, Excel may display it in general format (e.g., 1234.5) rather than the cell’s original formatting (like currency).

Tips

  • Use the CONCATENATE function for readability. =CONCATENATE(A2, " ", B2) does the same as =A2&" "&B2 but may be easier to read if you have many parts.
  • In newer Excel, use TEXTJOIN. =TEXTJOIN(" ", TRUE, A2:A10) joins a range with a delimiter and ignores empty cells.
  • Add line breaks. Use =A2&CHAR(10)&B2 and enable Wrap Text to put each value on a new line.
  • Insert special characters. Use CHAR() for characters like tabs (CHAR(9)) or non-breaking spaces (CHAR(160)).
  • Format numbers before joining. Use the TEXT function: =A2&" "&TEXT(B2, "0.00") to keep two decimals.

Troubleshooting

  • #NAME? error. This usually means you missed quotes around literal text. Check that all text is inside double quotes.
  • #VALUE! error. This can happen if you reference a cell that contains an error (e.g., #DIV/0!). Fix the source cell or use IFERROR to handle it.
  • Result shows as 0 or a date. Excel may interpret the formula as arithmetic. Ensure you have ampersands, not plus signs. If you see a date, the cell might be formatted as a date; change the number format to General.
  • Spaces disappear. If you join “John” and “Doe” but see “JohnDoe”, you likely forgot the space in quotes. Recheck your formula.
  • Formula displays as text. If the cell shows the formula instead of the result, the cell is formatted as Text. Select the cell, go to Home > Number Format, and choose General, then re-enter the formula.

Frequently Asked Questions

Can I join values from different worksheets?

Yes, reference the sheet name followed by an exclamation mark, e.g., =Sheet2!A2&" "&Sheet1!B2.

How do I add a comma and space between values?

Use =A2&", "&B2 (comma, space inside quotes).

Can I join a range of cells with the ampersand?

No, the ampersand works with individual cells or values. For a range, use TEXTJOIN or CONCAT (in Excel 2016+).

Why does my number lose its decimal places?

When you join a number, Excel uses the cell’s displayed format. If the cell shows 2 decimals but the underlying value has more, the joined result may show more. Use TEXT to control formatting.

Is there a limit to how many values I can join?

No, but very long strings may become hard to manage. Excel cell limit is 32,767 characters.

Can I use the ampersand with dates?

Yes, but the date may appear as a serial number. Use TEXT: =A2&" "&TEXT(B2, "mm/dd/yyyy").

Related

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

More cards in this drawer

The whole Numbers and Powers drawer →