Most of the functions in Excel are designed to help you save time when organizing and rearranging information in your spreadsheet. Concat and Concatenate are no different. While they are slightly unique in how they each work, both Concat and Concatenate can make it much easier to combine text from multiple cells into a single cell.
But if you’ve never used either function before, you’ve probably got a few questions. For instance, what are Concat and Concatenate? What’s the difference between these two Excel functions? Is one better than the other? What are the formulas to use them? Finally, where can you get help with Excel from trained IT experts?
In today’s guide, we will answer all of these questions and more, so let’s get started!
What Is Concat & Concatenate?
First, it’s important to understand that, at their core, Concat and Concatenate are two different versions of the same basic function. Prior to Excel 2016, Concatenate was the primary function used to combine strings of text from multiple cells into a single string of text in one cell. For example, let’s say you have a spreadsheet that looks something like this:
| A | B | C |
| 1 | Robert | Smith |
| 2 | Allan | Jones |
| 3 | Alicia | Williams |
| 4 | Morgan | Goldman |
| 5 | Kevin | Hainey |
If you wanted to combine the first names from Column A and the last names from Column B, you could do so by manually entering each string of text into Column C. Alternatively, you could use either the Concat or Concatenate formula to automatically combine the text from Columns A and B and display the new text in Column C, making your table look like this:
| A | B | C | |
| 1 | Robert | Smith | Robert Smith |
| 2 | Allan | Jones | Allan Jones |
| 3 | Alicia | Williams | Alicia Williams |
| 4 | Morgan | Goldman | Morgan Goldman |
| 5 | Kevin | Hainey | Kevin Hainey |
The Difference Between Concat & Concatenate
Other than the fact that Concatenate came first, you’re probably wondering how the two Excel functions differ. In truth, they do accomplish the same thing; they both combine text from multiple cells according to the parameters you choose. However, there are some important differences to consider.
Concat is a slightly modernized version of Concatenate that gives you a bit more flexibility. One of the major benefits of Concat is that it allows you to combine text using range inputs. So, instead of inputting each cell name into your formula, you can simply input a range and combine all of the text within that range. This is especially useful if you need to combine text from a lot of different cells.
It’s also important to consider that Concatenate is the legacy version of the function. While it still works as of this writing, it may not be supported in future versions of Excel. Therefore, if you’re just starting to learn how to use Concatenate and Concat, it’s probably best to focus on Concat, as it will likely be the primary way to combine text strings in Excel going forward.
The Concat & Concatenate Formulas
The Concat and Concatenate formulas are similar, but as previously mentioned, you can do a bit more with Concat. Here is the standard formula for the Concatenate function:
=CONCATENATE(Cell1,Cell2)
However, if you want to add space between the text of different cells, your formula will look more like this:
=CONCATENATE(Cell1,” “, Cell2)
Using the table from before, you could combine “Robert” and “Smith” by using this formula:
=CONCATENATE(A1,” “, A2)
Concat is virtually the same, but you’ll need to change the function name from CONCATENATE TO CONCAT.
Concatenating Dates In Excel
The Concatenate function works a little differently with dates, and there are various methods you can use depending on the desired formats you want. Let’s say that you have the day, month, and year in separate cells, but you want to combine them. It might look like this:
| Day | Month | Year | Date |
| 1 | 2 | 1 | 2020 |
| 2 | 14 | 6 | 2022 |
| 3 | 9 | 2 | 2021 |
| 4 | 10 | 10 | 2021 |
| 5 | 26 | 12 | 2019 |
To combine them using your desired date format, you would need to use the Concatenate function like this:
=CONCATENATE(C2,”/”,B2,”/”,D2)
Assuming that you combined the first row you could then click on the corner of your dated cell and drag your cursor down to apply the same formula to all of the necessary dates. The results would look like this:
| Day | Month | Year | Date | |
| 1 | 2 | 1 | 2020 | 1/2/2020 |
| 2 | 14 | 6 | 2022 | 6/14/2022 |
| 3 | 9 | 2 | 2021 | 2/9/2021 |
| 4 | 10 | 10 | 2021 | 10/10/2021 |
| 5 | 26 | 12 | 2019 | 12/26/2019 |
Note: You may need to use the Text function to set your preferred date format or even go into your Excel settings to change the default date format.
Concatenate With Delimiters
We’ve already covered how to add a space between different words or values when concatenating, but you might also want to add other delimiters, like commas or hyphens. To do this, you’ll just need to tweak your formulas a little bit.
Once again, here is a sample formula that adds a space:
=CONCATENATE(Cell1,” “, Cell2) or the same with CONCAT
Here is a sample formula that adds a comma, as well as a space after the comma for visual clarity:
=CONCATENATE(Cell1,”, “,Cell2) or the same with CONCAT
Finally, here is a sample formula that adds a hyphen:
=CONCATENATE(Cell1,”-“,Cell2) or the same with CONCAT
How To Do The Opposite Of Concatenate In Excel
Knowing how to split strings of text up is just as important as knowing how to join strings of text together. If you want to do the opposite of the Concat or Concatenate functions, you’ll have a few different options to choose from:
Text to Columns
- Click on the header of the column with the text you want to split
- Go to Data > Text to Columns
- Choose the split method: Delimited (adds delimiters) or Fixed Width (splits by specific column widths)
- If using Delimited, choose between spaces or commas
- If using Fixed Width, drag the column markers to where you want the split to occur
- Click “Finish” to finalize the split
Textsplit Function
The Textsplit function works the same as Text to Columns, but you’ll need to input the specific formula you want. Let’s say that you have the first and last names we outlined previously, but rather than putting them together, you want to split them apart. Your beginning spreadsheet might look something like this:
| A | B | C |
| 1 | Robert Smith | |
| 2 | Allan Jones | |
| 3 | Alicia Williams | |
| 4 | Morgan Goldman | |
| 5 | Kevin Hainey |
To split the text up, you’ll need to use the following formula:
=TEXTSPLIT(A1,” “)
Then, you can click on the results and drag your cursor to apply the same formula to all of Columns B and C. Then, your table will look like this:
| A | B | C | |
| 1 | Robert Smith | Robert | Smith |
| 2 | Allan Jones | Allan | Jones |
| 3 | Alicia Williams | Alicia | Williams |
| 4 | Morgan Goldman | Morgan | Goldman |
| 5 | Kevin Hainey | Kevin | Hainey |
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!