Excel - Using the CONCATENATE function

Objective

It is in a spreadsheet: Merge several data in a single expression.
These data are placed in various locations (including multiple sheets of the same file), and and you want to group them together to form a group of words or a phrase.

Procedure

• I have a list of names column A, the corresponding forename in column B and registration number in column C.
• In cell D2, if I put the formula =A2&B2, the following will be displayed "DUPONTAbélard."
• If I correct the formula by typing =A2&" "&B2, the following will be displayed "DUPONT Abélard".
• In cell E2, if I put the formula =A2&B2&C2, the following is displayed "DUPONTAbélard804."
• If I correct the formula by typing =A2&" "&B2&" N° "&C2, the following will be displayed "DUPONT Abélard N° 804".

Using a function

Instead of using the ampersand & symbol we can make use of the CONCATENATE() function, which gives the same result ...
The four formulas above would be written respectively

• =CONCATENATE(A2,B2)
• =CONCATENATE(A2," ",B2)
• =CONCATENATE(A2,B2,C2)
• =CONCATENATE(A2," ",B2," N° ",C2)

Further uses

If I add the following the phrase "is registered under the following number" to cell X1 of my spreadsheet and I want to have the following displayed in F2 phrase "DUPONT Abélard is registered under the following number 804", and then repeat the same action in all the cells of column F, I have the choice between the formulas:

• =A2&" "&B2&" is registered under the following number "&C2
• =CONCATENATE(A2," ",B2;" is registered under the following number ",C2)
• =A2&" "&B2&$X$1&C2
• =CONCATENER(A2," ",B2,$X$1,C2)

And if I set the name "afno" in cell X1,

• =A2&" "&B2&afno&C2
• =CONCATENATE(A2," ",B2,afno,C2)
• these six formulas can be copied down without any worries, Excel will automatically set the references of formulas.

Types of data used

• The result of the concatenation is in the text format
• The data are first converted into text before being merged.
• So you lose ALL formats specific display: percentage, decimal, date, time, phone,... all numeric values

Practical uses:

• Group first and last name into a single designation
• Group street, city and zip code in a single address line
• Create a phrase like "the young Abelard is 7 years old."
• Transform a number into string by concatenating with an empty string, for example =A1&"" is equivalent of =TEXT(A1,"Standard").

See also

Knowledge communities.

• Forum
• Excel forum
• Office software forum
• Programming forum

This document entitled « Excel - Using the CONCATENATE function » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.