Welcome back to the Excel Tip of the Week. As a reminder, one post each month will be posted here in IT Counts, but the regular weekly emails can be found in the Excel Community site. This month's post is a General User post in which we introduce the CONCATENATE function, which can be used to combine several pieces of text into one.
What it does: CONCATENATE is a text function. It can ‘glue together’ the contents of two or more cells which contain text, to create a single string.
How to write one: The syntax for a CONCATENATE looks like this:
A1 – This is the first bit of text to be included in the output B1 – This is the second bit of text to be included in the output (and so on) “Text” – If you want to have a specific bit of text included in the output, such as a space or a phrase, then you can include it by typing the desired text in quote marks instead of pointing at some external cell.
By adding additional commas and references / text, you can make the CONCATENATE longer. Unfortunately, this function doesn't work on arrays (i.e. you can't type =CONCATENATE(A1:F1) and join together the six selected cells).
You can also shortcut a CONCATENATE function by using the ampersand (&) symbol instead of a function. Using this version of the syntax, the example above would look like this:
What to use it for:
=“The total provision is £”&B1*C1&“.”
There are a couple of very small examples of this approach in the attached workbook. Previous post - Tables (further learning) TOTW index Next post - AND, OR This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.