Microsoft Excel is an extremely versatile tool that can be used for a wide variety of applications. When you think of Excel, you might immediately picture a spreadsheet full of confusing numbers. However, Excel is useful for text as well as numbers. The CONCATENATE Excel function is one of the many functions that you can use to manipulate text within your spreadsheet.
The purpose of the CONCATENATE function is really quite simple: it’s used to combine multiple text strings together, resulting in one piece of text. In this article, I demonstrate how you can use CONCATENATE to join text in your spreadsheet in whatever format you’re looking for.
Syntax
- Where text 1, text 2, etc. are individual pieces of text that you want to join together. These can be references to a cell containing text, a number, or text that you type directly into the equation between a set of quotation marks.
How to use the CONCATENATE function
To demonstrate how to use the CONCATENATE function, take a look at the example below. I want to combine first and last names into one text string.
I do this in the screenshot below with the formula =CONCATENATE(B2,” “,A2).
Notice that I’ve added a space between the names by using a space in quotation marks (“ “). If I didn’t do this, the first and last names would be combined together without a space inbetween.
Including the space, the function combines three different text items here. List each text string and separate them with a comma to join them all into one.
I’ll elaborate on this in the next example. Here, in addition to their full name, I want to write out how old each person is. I do this by taking the data from the spreadsheet (first name, last name, and age) and typing out the rest of the sentence within the formula.
Notice how I include the spaces within the text that I type in the formula. For example, before and after the “ is “ I have included a space, so that when it combines with the text before and after, the space is included.
Alternatively, you can add the spaces separately (use “ “ separated by a comma within the formula). However, it’s easier to include them with the other text in order to reduce the total number of items you’re combining. And if you forget a space somewhere (which happens all the time!) you’ll notice the mistake as soon as you finish the formula, and can easily go back and add it in.
CONCATENATE with numbers
Now that you’ve seen how to combine text in a few different ways, I’ll show an example that uses numbers.
When you include a number in the CONCATENATE function, you don’t need to put it in quotation marks – just separate it with a comma like you would any other item within the function. You can even perform a calculation within the function. That is exactly what I do in this example, by multiplying each person’s age by 12 to get their approximate age in months.
Notice how I didn’t need to add a new column in the data to perform the calculation to turn years into months – I do it right in the formula by typing =CONCATENATE(B9,” “,A9,”‘s age in months is “, C9*12).
Alternative to the CONCATENATE function: Ampersand (&)
Use the ampersand (&) instead of the CONCATENATE function for quick combinations of text. By putting an ampersand between the text you want to join, you instruct Excel to combine text without having to type out the CONCATENATE function.
I do this in the example below with this formula: =B2 & ” ” & A2.
The choice between using the Ampersand method and the CONCATENATE function really comes down to personal preference: they both work well for combining text, so pick whichever one you prefer!
CONCATENATE with TEXT funtion
Combine the TEXT function with the CONCATENATE function to format numbers in your text.
Take a look at the example below. I include the TEXT function in the TODAY function to indicate the day of each person’s birthday (just assume they all share the same birthday for this example!).
The formula I use here is =CONCATENATE(B2,” “,A2,”‘s birthday is “,TEXT(TODAY(),”DDDD”)).
That’s how to use CONCATENATE in Excel
With the CONCATENATE Excel function, you can join all kinds of text. This includes cell references, text typed into formulas, and numbers.
On top of that, you can even perform calculations and format numbers within the formula. Who said Excel was just for numbers?!
