How to Join Cell Values with CONCATENATE

Updated 13 November 2026 · Standard formula, shown below

This guide shows you how to combine text, numbers, and cell references into a single string using the CONCATENATE function in Excel and Google Sheets. You will learn the exact syntax, how to add separators like spaces or commas, and how to avoid common pitfalls that cause errors or unexpected results.

What You’ll Need

A spreadsheet application (Microsoft Excel 2016 or later, Google Sheets, or LibreOffice Calc).
A worksheet containing the cell values you want to join.
Basic familiarity with entering formulas (starting with an equals sign).

Step-by-Step

Identify the cells to join. For example, you have first names in column A (A2:A10) and last names in column B (B2:B10). You want full names in column C.
Select the cell for the result. Click on the cell where the combined text should appear, for instance C2.
Type the CONCATENATE formula. Begin with an equals sign, then the function name, and an opening parenthesis: =CONCATENATE(.
Add the first cell reference. Click on A2 or type A2 after the parenthesis.
Add a separator (optional but common). To put a space between the two values, type a comma, then a double-quoted space: , " ". If you want a comma and space, use ", ". If no separator is needed, skip this step.
Add the second cell reference. Type a comma, then click on B2 or type B2.
Close the formula. Type a closing parenthesis: ). Your formula should look like: =CONCATENATE(A2, " ", B2).
Press Enter. The combined value appears in the selected cell. For the example, it will show “John Smith” if A2 is John and B2 is Smith.
Copy the formula down. Click on the cell with the formula, then drag the fill handle (the small square at the bottom-right corner) down to apply it to the rest of the rows. Alternatively, double-click the fill handle to auto-fill the column.
Adjust for more cells. To join more than two values, simply add more arguments separated by commas. For example, to combine A2, B2, and C2 with a hyphen: =CONCATENATE(A2, "-", B2, "-", C2).

Common Mistakes

Forgetting the equals sign. Without it, the text is treated as a plain string, not a formula.
Omitting commas between arguments. Each argument must be separated by a comma. Forgetting one causes a #VALUE! error.
Using single quotes for text. In Excel and Sheets, text must be enclosed in double quotes, not single quotes. Single quotes are used in some programming languages, not in spreadsheet formulas.
Adding spaces manually. If you type =CONCATENATE(A2 B2) (without a separator), you get an error. You must explicitly include a space as a separate argument.
Mixing cell references and text incorrectly. If you type =CONCATENATE("A2", " ", "B2"), you will get the literal text “A2 B2” instead of the values in those cells.

Tips

Use the ampersand (&) for shorter formulas. The & operator does the same thing as CONCATENATE. For example, =A2 & " " & B2 is equivalent and often easier to type.
Consider CONCAT or TEXTJOIN. In Excel 2016 and later, and in Google Sheets, the CONCAT function is a simpler alternative (though it doesn’t accept separators). TEXTJOIN (available in Excel 2016 and Sheets) lets you specify a delimiter and ignore empty cells, which is great for lists.
Format numbers before joining. If you join a number with text, the number may lose its formatting (e.g., 1234.5 might show as 1234.5). Use the TEXT function to format it: =CONCATENATE("Total: ", TEXT(A2, "$#,##0.00")).
Use line breaks. To insert a line break between values, use CHAR(10) in Excel (or CHAR(10) in Sheets, but you must enable wrap text). For example: =A2 & CHAR(10) & B2.
Trim extra spaces. If your source cells have trailing spaces, use the TRIM function: =TRIM(A2) & " " & TRIM(B2).

Troubleshooting

Formula shows as text. If the cell displays the formula as text (e.g., “=CONCATENATE(A2, ” “, B2)”), the cell is probably formatted as Text. Select the cell, go to the Home tab (or Format menu), change the number format to General, then re-enter the formula (press F2 and Enter).
#NAME? error. This usually means the function name is misspelled (e.g., CONCATENATE with one T). Check the spelling. In some non-English locales, the function may have a localized name (e.g., CONCATENER in French).
#VALUE! error. This often occurs when an argument is not valid. Common causes: using a range instead of a single cell (e.g., CONCATENATE(A2:A5)), or missing commas. Ensure each argument is a single cell, a quoted text string, or a number.
Unexpected numbers like 42358. If you join a date, Excel may show the serial number. Use the TEXT function to format the date: =CONCATENATE("Date: ", TEXT(A2, "mm/dd/yyyy")).
Results not updating. If the source cells change but the result doesn’t, check that calculation is set to Automatic (Formulas tab > Calculation Options). If it’s Manual, press F9 to recalculate.

Frequently Asked Questions

What is the difference between CONCATENATE and CONCAT?

CONCAT is a newer function that does the same basic joining but without the option to add separators directly. It also allows a range of cells, e.g., =CONCAT(A2:A5). CONCATENATE is still supported for compatibility but may be phased out in future versions.

How do I add a comma and space between values?

Include a separator argument: =CONCATENATE(A2, ", ", B2). The comma and space are inside double quotes.

Can I join values from different worksheets?

Yes, reference the sheet name followed by an exclamation mark: =CONCATENATE(Sheet2!A2, " ", Sheet1!B2). If the sheet name has spaces, enclose it in single quotes: ='Sheet 2'!A2.

Why does CONCATENATE show a number as 0.5 instead of 50%?

Because the underlying value is a decimal. To display a percentage, use the TEXT function: =CONCATENATE("Score: ", TEXT(A2, "0%")).

How do I ignore empty cells when joining?

Use the TEXTJOIN function with the ignore_empty parameter set to TRUE. For example: =TEXTJOIN(" ", TRUE, A2:C2). This joins all non-empty cells in the range with a space.

Can I use CONCATENATE to combine a column into one cell?

Yes, but CONCATENATE cannot take a range. Use CONCAT or TEXTJOIN. For example, =TEXTJOIN(", ", TRUE, A2:A10) will combine all values in A2:A10 with a comma and space.

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