

Excel offers three distinct formulas as well as a fourth way to combine multiple text cells into one cell. There are countless examples in which you might need this: Combine given– and family names or preparing primary keys for multi-conditional lookups. For example, in a VLOOKUP or INDEX/MATCH formula combination. In this article you learn 4 methods and in the end, you learn how to deal with a large range of cells.
Contents
Example for the CONCAT formula.
The CONCAT formula has been introduced to Excel with the version 2016. It’s not available on previous versions of Excel. And that’s already the biggest disadvantage. Besides that, this formula is very useful.
The CONCAT ist he sucessor oft he CONCATENATE formula and has at least one and at maximum 254 arguments. It can handle separate cells as well as cell ranges. It’s even possible to combine single cells with cell ranges, e.g. =CONCAT(B4,C4:D4) .
As you can see in the screenshot above, using the CONCAT formula is very easy. Just refer to the cells you’d like to combine.
Example for the CONCATENATE formula.
Unlike the CONCAT formula, CONCATENATE is available in older versions of Excel. Microsoft says within the help section of the CONCATENATE formula, that the CONCATENATE is replaced by CONCAT. CONCATENATE is only kept in Excel in order to guarantee the compatibility to older versions on Excel and it’s recommended to use CONCAT instead.
CONCATENATE works almost the same way like CONCAT with one major difference: It’s not possible to use ranges of cells as references. Only single cells can be combined. The maximum number of arguments and therefore single cell references is 255.
Structure of the TEXTJOIN formula.
Since Excel 2016 there is another, advanced option to combine text in Excel. The formula is called TEXTJOIN. Besides simply putting text together, the formula offers two advanced options:
The structure of the TEXTJOIN formula is shown in the figure above. The formula has at least three arguments.
Example of the TEXTJOIN formula.
The screenshot of the example on the right-hand side shows an example for the TEXTJOIN formula. The first part is skipped which means that there is no delimiter. The second argument is set to FALSE so that blank cells aren’t ignored. Eventually the last argument refers to the cell range B4 to D4 which contains the cells to be combined.
Example of the &-sign to combine values.
The easiest way is probably to just use the “&”-sign to combine values in Excel. This method has the same disadvantages like the CONCATENATE formula from the method 2 above. It can only regard single cells and not ranges of cells. An advantage of this method is that it’s usually easier to follow up the calculation steps.
As you can see in the screenshot above, you can just refer to several cells and combine them with the &-sign. That’s it.
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Let’s say, you want to combine 1,000 Excel cells into one cell. The good news: You can use all four methods to accomplish this, even in a simple manner. There is one restriction though: One Excel cell can’t contain more than 32,768 characters.
For combining 1,000 cells in Excel, you can use two basic approaches:
Example of how to combine 1,000 cells using the CONCAT formula.
Use method 1 (CONCAT formula) or method 3 (TEXTJOIN formula) above which can regard cell ranges. The solution using the CONCAT formula is shown in Figure 66. As noted before, the only requirement of this method is that you have to use Excel version 2016.
When using the &-sign for combining 1,000 cells, the easiest way is to insert a helper column.
Insert a helper column (or row, depending on how your data is organized) which always combines the current cell with the previous combination of all cells so far.
As you can see in the image on the right-hand side, the helper column (here in cell G6) combines cells G5 (which contains the previous combination) with the current cell F6. The solution in cell I3 only links to the last row in the helper column.
All the examples above can be found in this example workbook.
Please click here and the download starts immediately.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 2
Chris Goldie
Please click here and the download starts immediately.
The above-mentioned link on this page is not working.
Henrik Schiffner
Hi Chris,
Thanks for the notice. I suppose, you are using Google Chrome, right? It seems, Chrome blocks this download. It should be fixed now.
Best regards,
Henrik