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."
, the following will be displayed "DUPONTAbélard." If I correct the formula by typing =A2&" "&B2 , the following will be displayed "DUPONT Abélard".
, 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."
, 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: