A to Z of Excel Functions: The CONCATENATE Function
22 September 2017
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CONCATENATE function.
The CONCATENATE function
IMPORTANT: In Excel 2016, Excel Mobile, and Excel Online, this function has been replaced with the CONCAT function (see the last functions blog). Although the CONCATENATE function is still available for backward compatibility, you should consider using CONCAT from now on. This is because CONCATENATE may not be available in future versions of Excel.
CONCATENATE may be used to join two or more text strings into one string. This will link text with the values in cells and / or the results of various formulae. For example, consider the following:
This shows how simple concatenation is. Using the CONCATENATE function, the above example is no more difficult than:
=CONCATENATE(“You have picked the colour ”,G11)
Note that the text has to be in inverted commas and if you require spacing this needs to be built into the text component too.
This formula could be simplified using the ampersand (&) operator instead:
=”You have picked the colour ”&G11
This alternative formula would do exactly the same thing and does not require you to learn how to spell CONCATENATE. Seriously, the operator is preferred in any case since it is slightly more efficient and allows for more items to be joined together.
For the more pedantic amongst you, the text above does not form a sentence. Indeed, it also treats “Orange” as a proper noun as it capitalises the “O”. Also, there is no full stop. Therefore, we might wish to refine the above example as follows:
In this example, I have generated the more elaborate formula, viz.
="You have "&IF(G11="","not yet picked.","picked the colour "&LOWER(G11)&".")
Working our way through this formula, this version allows for what happens if cell G11 is unpopulated (i.e. the expression G11="" means G11 is blank / empty), inserts the full stop and turns “Orange” into “orange” using Excel’s LOWER function (this makes everything lower case).
Concatenation often means manipulating the appearance of text: as well as the LOWER function, UPPER (upper case) and PROPER (pseudo-title case: capitalises the first letter of each and every word) can be useful partners in crime.
Concatenation is often referred to as “mixed text”, in that it is a mixture of text and other arguments such as cell references and / or formulae results. Typically, mixed text will often contain numbers too, and these can be a little more awkward to format. You cannot simply Format Cells (CTRL + 1) as this formats cells containing numerical data only.
In this case, we have to fall back on the useful TEXT function which formats, er, numbers. The syntax of TEXT is as follows:
The reference is the cell or formula that needs to be formatted and the Mask is the syntax you would enter for Custom number formatting in the Format Cells (CTRL + 1) dialog box:
To see how this works in practice, consider the following:
Again, the eagle-eyed will spot no full stop, but that is not the point here. The formula
="The number is "&TEXT(G25,"$#,##0.0,,\m")
takes the value in cell G25 and displays it to the nearest $0.1m.
Speaking from experience, with practice much mundane monthly reporting commenting on numbers can be automated using this technique; the key is to identify all the scenarios that may occur and allow for them. The formulae may get slightly complex, e.g.
This example commentary considers how revenue has changed from one month to the next:
=IF(Prior_Period_Revenue=0,"Revenue is "&TEXT(Current_Period_Revenue,"$#,##0")&" this month.",
IF(OR(Prior_Period_Revenue<0,Current_Period_Revenue<=0),"Revenue inputs need to be reviewed.",
IF(Prior_Period_Revenue=Current_Period_Revenue,"Revenue remains constant at "&TEXT(Current_Period_Revenue,"$#,##0")&" for the month.",
"Revenue has "&
" by "&TEXT(ABS(Current_Period_Revenue/Prior_Period_Revenue-1),
"#,##0.0%")&" this month.")))
The examples cited above can all be found in the attached workbook. Good luck working that final formula through!
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.