Concatenating text in Excel

Concatenating text simply means joining different cell contents or different bits of text into one single cell. This can be a valuable way of presenting data more clearly and save time and effort sifting through complex spreadsheets.

To see one of the ways in which text concatenation can be useful, let’s consider an example. Below we have a profit or loss value (B1) and a cell (A1) that returns the word ‘Profit’ or ‘Loss’ according to whether that value is above or below zero.

Elsewhere in our sheet we want to include the sentence: ‘The profit for the year was £11,000,’ with the word ‘Profit’ and the value coming from cells A1 and B1, so the text sentence will change automatically if the figure alters:

There are two main ways to achieve this. We can use the CONCATENATE() function:

=CONCATENATE(“The “,A1,” for the year was “,B1)

Or the & (ampersand) which allows you to join text without using a function:

=”The ” & A1 & ” for the year was ” & B1

Note that in both cases, any actual text has to be surrounded in double-quotes and we need to remember to enter the spaces between parts of our text.

Our result is not yet complete. We need to use the TEXT() function to specify the format of the profit value using a custom format:

=”The ” & A1 & ” for the year was ” & TEXT(B1,”£#,##0″)

Let’s see what happens (automatically) when our number changes to a negative value. Our example includes the CONCATENATE() and & method to show they give the same result:

Let’s see what automatically happens, if we use the CONCATENATE function, when our profit value changes to a negative value:

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.