How to Use Excel's Concatenate Function
Use CONCATENATE or an ampersand in Excel when you need to merge text, formulas and cell references into a single output.
Excel 2010 and 2013's CONCATENATE function merges two or more strings of text into a single cell. Despite the term "strings of text," the function isn't limited to plain text. Use CONCATENATE to merge the results of multiple formulas, cell references and pieces of plain text into one cell.
Write a CONCATENATE Formula
To use the CONCATENATE function, write a formula starting =CONCATENATE( followed by each item you want to combine, separated by commas. To include strings of plain text, wrap the text inside quotation marks. For example, the formula =CONCATENATE("x","y") results in a single cell that reads "xy."
You could simply type "xy" in a cell. CONCATENATE's real power lies in combining other formulas and cell references. For example, use =CONCATENATE(A1, "x") to merge the value in cell A1 with the letter x. CONCATENATE also works well to give results in full sentences: Use =CONCATENATE("The sum of column A is: ",SUM(A:A)) to display column A's sum in a complete sentence.
- CONCATENATE does not automatically include a space between each comma-separated item, so make sure to type spaces at the beginning or end of your text strings when necessary. In "The sum of column A is: " example, note the space after the word "is."
- CONCATENATE isn't limited to two items. =CONCATENATE("a","b","c","d","e") works to display the result "abcde."
Use the Ampersand Shortcut
The ampersand operator serves as a shortcut for the CONCATENATE function, performing the same task with fewer characters. Instead of typing CONCATENATE( and placing commas between each item, place an & between each of the items. Other than this change, the cell behaves identically to one using the regular CONCATENATE method.
You still need to start the line with an equals sign and enclose plain text in quotation marks when using the ampersand shortcut. Do not encase the entire line in parentheses.