This is a wonderful new function in Excel that replaced theCONCATENATE function in Excel 2016. It combines the contentsof separate cells together into one cell. For instance, you have a listin Excel where first names are in column A and the surnames are incolumn B. You would like to combine these two into one cell, separatingthem with a space or possibly putting the surname first, separated by acomma, then the first name of the person. You can join up to 255strings/texts together using CONCAT.
The CONCAT function allows you to join several textstrings into one! Open the CONCAT.xlsx file and proceed asfollows:
- We will use the CONCAT function to combine thefirst names in column B with the surnames in column C.
- The result will be collected in cell L5. Click into cell L5. Type=CONCAT(B5,C5).
- Press Enter to view the result. Note that the name andsurname have combined into cell L5, but we need to use aseparator to create distance between the two words.
- Let’s edit the formula. Double-click on cell L5. We will add a spacein between the arguments referring to cells B5 and C5. The space wouldneed to be enclosed by inverted commas, as you have learned previously.The new formula will read =CONCAT(B5,””,C5), as illustrated in the following screenshot:
“
- Press Enter to view the new result. Copy the formula downusing AutoFill so that all the namesand surnames are combined with a space in between.