Excel Tip of the Week #14 - CONCATENATE

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
– 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:

  • Creating an address from a list of address parts, or a single name from a list of first and surnames
  • Embedding a calculated number or a reference into a sentence, e.g. by putting:

          =“The total provision is £”&B1*C1&“.”

There are a couple of very small examples of this approach in the attached workbook.

Previous post 
TOTW index
Next post

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.