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:

=CONCATENATE(A1,B1,“Text”)

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:

=A1&B1&“Text”

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 - 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.

Anonymous