The CONCATENATE function in Excel helps you more easily manipulate text-based cells. While there are plenty of functions of performing calculations on numbers, this function helps bring together multiple text cells into a single cell. No matter what the text is, you can combine it without changing anything in the original cells.
Before You Use the Concatenate Function in Excel
As with most Excel functions, there are some rules and restrictions. The first is the name of the function itself. If you’re using Excel 2016 and later, you can use either CONCATENATE or CONCAT. CONCAT is easier because it’s shorter, but you can use the two interchangeably. For Excel versions older than 2016, you must use CONCATENATE.
There is a limit to how much you can combine into a single cell. Each CONCATENATE function can have up to 255 items, equaling up to 8,192 characters. Unless you’re a power user, you probably won’t come anywhere close to those totals.
While many Excel functions let you use arrays, CONCATENATE isn’t one of those. Every cell must be referenced. So if you wanted “B3:B9”, you’d need to list each cell individually in your function.
If any of the cells you want to combine contain numbers, such as a house number, the function treats them as text. The combined cell will be a text format only.
Finally, if any of your text strings have special characters in them, the function will try to use them as part of the function versus seeing them as a text string. Special characters must be placed in quotations. This also includes if you want any of your cells separated by a special character in your combined cell.
Using the Concatenate Function in Excel
The basic syntax couldn’t be simpler. Naturally, things can get more complex.
The most basic function looks like this, with your desired cells of course:
=CONCATENATE(A1, B1, C1, D1)
or
=CONCAT(A1, B1, C1, D1)
This would combine cells A1, B1, C1, and D1 together. This syntax assumes all four cells are simple text with no special characters, dates, or other specially formatted numbers.
As you may notice, the cells are combined, but there aren’t any spaces between them, which makes the entire line difficult to read. To add a space between cells, do the following:
=CONCATENATE(A1, " ", B1, " ", C1, " ", D1)
This adds a simple space and nothing more.
You can also include other text phrases and special characters. For example, you could add commas and the word “and” to create a better sentence using the following:
=CONCATENATE(A1, " ", B1, ", ", C1, ", and ", D1)
This makes the combined cell make more sense as a sentence, though this isn’t always necessary depending on what you’re combining.
Working with Dates
If you’re working with dates and other specially formatted numbers, you’re not going to get the results you want using the basic CONCATENATE function in Excel. For example, when using:
=CONCAT(A10, " ", B10)
You get the following result.
Since the cell isn’t actually a text-based cell, you have to make the function see the cell as text. To get the right result, you’d need to use the following:
=CONCATENATE(A10," ",TEXT(B10,"MM/DD/YYYY"))
TEXT tells the function to treat the following cell as text. The TEXT function must be formatted as (Value, “format”). In this case, the value is cell B10 and the format is the date format. Microsoft has a list of different TEXT functions you may need to use with CONCATENATE.
As with most Excel functions, you can select the cell you placed the original function in and drag it down to copy the function to use with other cells, such as combining a list of names and numbers together down a spreadsheet.
While CONCATENATE is one way to combine cells, there are other methods which may work better based on your needs. Of course, you can also split cells apart, too.