CONCAT Function in Excel
A commonly used function in Microsoft Excel that allows you to merge (or “concatenate”) multiple strings from different cells into a single cell.
What Is CONCAT Function In Excel?
The CONCAT function in Excel (CONCATENATE in pre-2016 versions of Excel) is a commonly used function in Microsoft Excel that allows you to merge (or “concatenate”) multiple strings from different cells into a single cell.
CONCAT is widely used in data analysis tasks, where text strings may need to be combined to create complete descriptions, such as a full name or address. It is most effective when working with large datasets, as it can merge information across multiple sheets.
Although it is a relatively simple and easy function, knowing the limitations and techniques for using CONCAT is essential to get the most utility.
The function (called CONCATENATE in pre-2016 versions of Excel) is an excel formula that combines text strings from multiple cells into a single cell.
It can concatenate a maximum of 253 arguments of any length from any source, including cells from another spreadsheet or text entered directly into the formula.
This is a powerful ability when analyzing data, enabling you to create complete descriptions of attributes with minimal manual labor.
The function is a fairly simple function to use.
The function takes multiple values of the same argument. The syntax for the CONCAT function is:
=CONCAT(text1, …)
Where,
- text1 = Cell containing the first text string to concatenate
The function can take multiple cells in the function as long as commas separate them. The following section will examine an example data table and the function in action.
CONCAT Function Examples
Below is a table containing data on random football players in the NFL.
Each column contains a string of text regarding certain information about the players: Their Name, Position, Team, & Number. As we can see, the table separated the first and last names into different columns.
Let’s combine these columns to describe the player’s name completely. First, we’ll create a new column next to the “Last Name” column to use our CONCAT formula:
To use our CONCAT formula, we can begin by typing in our “=” sign followed by CONCAT:
To concatenate our name cells, we can select the cells A2 and B2 and separate them with a comma:
We officially concatenate two cells! But if we look closer, there’s no space between Tom & Brady. This is a significant limitation of the CONCAT function.
The CONCAT formula does not account for spaces automatically, so we must manually add them. This can quickly be done by returning to our original formula and adding a comma, then a space in quotations:
The resulting output from the function is “Tom Brady”:
We have now concatenated the name cells successfully. We can easily autofill the rest of the column by dragging our cursor to the bottom right of the completed cell and double-clicking on the little green square that appears.
Now let’s make it a little bit more complicated. What if we wanted to make a statement based on the data table? That would require us to merge many more cells and add more words to the formula.
Instead, let’s use the data table to write a sentence describing each player's position and team. We can start by determining the order we want to concatenate each cell.
We want our first statement: “Tom Brady is the QB of the Tampa Bay Buccaneers.” We would have to add the words “is the” and “of the” directly into the formula.
The function can take text that's been manually inputted into the function and concatenate them with the cells.
We also have the team name before the team location, meaning we must be wary of the order we concatenate. Ultimately, we will have a formula that looks like this:
An important thing to note is that the spaces were incorporated directly into the connecting words “is the” and “of the” to make our formula look cleaner.
Since we didn’t want to put a space between our last word and the period, we could add it to our function with no precaution.
We can again autofill the rest of our table by double-clicking the little green square in the bottom right-hand corner of our completed cell.
Special CONCAT Cases
Concat is a simple formula to read and use, but we can do more complex tasks with special operators and understand its limitations.
We will look at a couple of them here:
1. The “&” Operator
The “&” operator works as a good substitute for the CONCAT function as it works in all the same ways as our formula. It is generally considered by Excel professionals to be better practice to use the “&” operator in comparison to the commas that come with CONCAT.
Let’s see an example of our “&” operator in action.
Returning to our table, let’s say we want to manually concatenate the team names and locations. This can quickly be done by substituting “=CONCAT(...)” with “=[cell1] & [cell2]”, where the cells 1 & 2 are the cells we want to concatenate:
As we can see, the ampersand has the same limitations as our CONCAT function. The cells are combined without adding a “ “ between the cells we wish to concatenate.
2. TEXTJOIN Function
Another useful alternative for the CONCAT function is Excel's “TEXTJOIN” function. The TEXTJOIN function allows you to preset a delimiter, a value to separate the data you want to split. The TEXTJOIN function takes on multiple arguments:
“=TEXTJOIN(delimter, ignore_empty, text1, …)”
Where,
- delimiter = the value you wish to have to separate the cells you are merging
- ignore_empty = An argument determining whether empty cells are included in the output. It takes the value of either True or False.
- text1… = the cells you want to merge
The TEXTJOIN function removes the repetition we have in the CONCAT function. Let’s see the TEXTJOIN function in action:
We have a list of fruits in the table above. We would like to concatenate them into a comma-separated list in one cell using TEXTJOIN.
We can do this by setting our delimiter as “, “ and setting our “ignore_empty” argument to true (It is a good practice to keep this set to “TRUE” by default). Then we can insert our column as a range:
As we can see, the TEXTJOIN function is a much more efficient alternative to CONCAT. It removes the repetitive action of manually inputting a space between cells you are merging.
Limitations of CONCAT
A big drawback of CONCAT is the loss of number formatting when combining cells. Let us set the stage with a demonstration below:
Above, we have a table of individuals and their dates of birth.
If we were to try to concatenate the cells in the first row together to make a statement that says, “Adam Harold was born on 2/2/1980.” based on the information we have, we would get a function like this as a result:
As you can see, the function fails to correctly format the date, providing us with a serial number instead. This is a significant limitation to consider when using the function.
To work around this limitation, we can nest a couple of other functions within CONCAT to properly format our text, so it properly describes our data.
For this, we will nest a TEXT function to format our text how we want it. The TEXT function takes 2 arguments:
“=TEXT(value, format_text)”
Where
- value = the cell we wish to format
- format_text = the format in which we want the text in our cell formatted.
Nesting this into our function is as simple as adding it into the argument we want this to replace. This leaves us with the following as our function:
As we can see, we want the date of birth to be in the month/day/year format, so we entered “mm” for the month, “dd” for the day, and “yyyy” for the digits that make up the year. So this formats the cell in our sentence:
We can easily autofill this for the rest of the table by highlighting the cell and double-clicking on the bottom right corner.
That is how we can mitigate one of Excel's biggest drawbacks of the CONCAT function.
If you are interested in learning more about CONCAT and its special cases and operators, check out our Excel Modeling Package so that you can become an Excel Pro!
Conclusion
The CONCAT function in Microsoft Excel is a beneficial formula for merging text strings from multiple cells into a single text string.
This function is best used when performing data analysis tasks to obtain a full description based on bits and pieces of data across multiple tables.
The formula syntax is
“=CONCAT(text1…)”
Where text1 refers to the first of multiple text cells or other strings you want to concatenate. You can also use the “&” operator instead of CONCAT to concatenate cells together manually.
When using CONCAT in Excel, it is important to remember that cell formatting will be changed to the base text. This includes numbers and other variables. Therefore, you will want to nest the TEXT function into the formula to fix this.
You should also know that the CONCAT function fluctuates, so changes to connected cells will cause changes to the function, potentially affecting the performance of the device you are working on if you are working with a large data set.
CONCAT Function FAQs
CONCAT is a function in Excel that allows you to merge text strings from multiple cells into 1 cell easily.
The syntax for the function is denoted as “=CONCAT(text1,…) where “text1” represents the first cell of the multiple you wish to concat.
The “&” operator allows you to manually concat 2 cells without using the “CONCAT” function.
The syntax for the operator is denoted as “= [cell 1] & [cell 2],” where 1 & 2 are the cells you wish to merge. This can be repeated as needed to merge cells.
Excel's “TEXTJOIN” function is an easier way to concat multiple cells. You can set a value (known as the delimiter) beforehand that separates the cells you are concatenating.
The #NAME! Error usually occurs when we mistype a formula or a cell name. A possible solution would be to attempt to clear the cell and restart the formula.
The #VALUE Error occurs if the result of the CONCAT function is a string that exceeds the cell limit.
This is because Excel’s CONCAT formula does not automatically add any spaces. Instead, you must add a “ “ between cells you want spaces between when using the “=CONCAT” function.
or Want to Sign up with your social account?