Generally, the information is placed in different cells in Excel sheets for better organization of data. In certain scenarios, you may require a single string that is the combined result of various cells. For example, storing First Name and Last Name in different cells.
You may require full name result combined in a single cell. This is where Excel CONCATENATE function plays its role. The CONCATENATE function combines two or more text strings (as constant literals) or from different cells and returns a single combined string.
You may also use ‘&’ ampersand sign for concatenating string/cells in Excel. Both CONCATENATE and ‘&’ produce the same results.
Syntax of CONCATENATION function
1 =CONCATENATE("The Marks of Student ", B2, " ",C2, " is ", D4)
The above syntax shows using a text string enclosed in double quotes and various cells separated by commas. You may also use numbers without double quotes in CONCATENATE function.
See the section below for various examples of using the CONCATENATE function which is followed by using ‘&’ sign.
An example of combining text strings
In the first example, I will simply join a few text strings given in the CONCATENATE function. The formula is applied in the B2 cell. Have a look at the CONCATENATE formula and result:
1 =CONCATENATE("An ", "Example ", "of ", "CONCAT ", "Text Strings ")
The example of concatenating cells
For the example, I will use an Excel sheet that stores student result information. The columns include Student ID, First Name, Last Name, subject marks and Total marks.
By using the CONCATENATE function, I will join First Name, Last Name, and Total as follows:
1 =CONCATENATE("Total marks of ", B2, " ", C2, ," is ", G2)
The resultant sheet of data:
You can see, the C8 cell displays the concatenated result based on above formula. There, I also used space by “ “ to be used in concatenation.
The example of using numbers in CONCATENATE formula
The following example shows using the numbers, text strings and cell references in the CONCATENATE formula:
1 =CONCATENATE("Number ", 1," is ", B2)
The output of above formula in our example sheet:
An example of using ampersand sign for concatenation
As mentioned earlier, you may also use the ‘&’ sign for concatenating cells or text strings, and numbers in Excel.
The following example uses the same Students Excel sheet and this time I will display the total of student “Hina” by concatenating different cells and strings:
The Formula for ‘&’
1 ="Total marks of "& B4 &" " & C4 &" is "& G4
You can see, the result is achieved as we got by using the CONCATENATE function. In the case of ‘&’, no commas are used for separating the cells or text string. Besides, no parenthesis is used.
You may use any of the technique that you are comfortable with.
Concatenating strings with line breaks example
You may add the line breaks by using the CHAR(10) function in Excel. In the following example, the CHAR(10) function is used with the CONCATENATE function for adding line breaks. For that, the first and last names are displayed on the first line.
The second line contains marks in Physics. The third line contains Chemistry marks and so on.
1 =CONCATENATE( B2, " ", C2, CHAR(10),"Physics = ", D2, CHAR(10), "Chemistry = ", E2,CHAR(10), "Biology = ", E2)
The resultant sheet:
Make sure that you enable the “Wrap Text” option for the cell where you are applying this formula. Otherwise, it will display the output on the same line. You may see the “Wrap Text” option under the Home tab in the ribbon (as shown in above graphic).
Alternatively, press Ctrl + 1 or right-click in the cell where you are using this formula. In the menu, select “Format Cells” option. In both cases, it should open the “Format Cell” dialog. Go to the Alignment tab and check the “Wrap Text” option as shown below:
Using other formulas returned values in CONCATENATE function example
The following example is even more complex that uses INDEX and MATCH functions in an Excel sheet that contains dummy product information.
As you enter a product name to search (with wildcards), the formula returns product information that includes Product name, price, and its status. First, have a look at the formula and resultant sheet and I will explain how it worked:
The CONCATENATE / INDEX / MATCH/ CHAR formula:
1 =CONCATENATE("Product Name =",INDEX($B$2:$B$11,MATCH(D13,B2:B11,0)),CHAR(10),"Price = ", INDEX($C$2:$C$11,MATCH(D13,B2:B11,0)),CHAR(10),"Status = ",INDEX($D$2:$D$11,MATCH(D13,B2:B11,0)))
Looks big formula, is not it? You may break this formula for simplification as follows:
This formula returns the product name. You can see, I searched “S*” that found Sugar. Similarly, INDEX/MATCH functions are used two more times for returning the price and status, respectively.
All these formulas are placed inside the CONCATENATE function with CHAR(10) for adding line breaks for proper formatting.
You may download this example sheet and try searching different products and see the outcome. Give exact names or search by wildcards (* and ?).
Concatenating range of cells by CONCAT function
As mentioned earlier, the CONCATENATION function is replaced by CONCAT from Excel 2016. The CONCAT function allows concatenating the range of cells as well. On the other hand, the CONCATENATE has no direct way of joining the range of cells. You have to write CONCATENATE formula like this:
=CONCATENATE(B1, B2, B3, B4, C2, C2,….)
Or you may press the Ctrl key and select the cell that you want to concatenate one by one. For that, enter the =CONCATENATE in the cell or formula bar and start selecting cells that you want to include for joining. As you are done, release the Ctrl key and press enter.
As for as CONCAT is concerned, you may provide the range as follows:
It will return all values concatenated from cell A2 to B10. However, this formula is applicable from Excel 2016.
How to include double quotes in the concatenated string?
You have seen in the example that text strings are enclosed in double quotes. What if your text string itself contains double quotes?
The following example shows using double quote in the string within CONCATENATE function.
1 =CONCATENATE(B2,""""," Pizza")
You can see, using double quotes four times added one in the result. Also note, if your cells contain double quoted text, Excel manages that itself and there should not be an issue.