CONCAT
The CONCAT function joins text located in different cells. The technical term is to Concatenate. This page will show three different ways to Concatenate text from a range of cells and introduces some other related functions and features.
Using the CONCAT function
Let’s say you have a customer list with the First Name in cell A2 and the Last Name in cell B2. In cell D2 enter the function:
=CONCAT(A2,B2) The result will look something like: BillRogers
Note that there is no space separating the first and last name. To add the space character enter the function:
Using the & Character
Again , let’s say you have a customer list with the First Name in cell A2 and the Last Name in cell B2. In cell D2 enter the formula:
=A2&B2 As with the Concat function the result will look something like: BillRogers
Note that there is no space separating the first and last name. To add the space character enter the function:
You can now copy the formula down the entire row in in the list
Both the CONCAT function and “adding” or “joining” cells with the & (ampersand) character accomplish the same thing. Using the & character is fairly standard in the computer world, in programming and other related uses where the CONCAT function is an Excel function with intent of simplifying the process.
Using the CONCATENATION Function
The CONCATENATION function is the earlier version and is replaced with the CONCAT function. The CONCATENATION function limited to 30 individual references.
What is the Better Choice?
As you can see, the three approaches are about the same so it’s a matter of personal preference. Since CONCATENATION is an older function and has limitations it’s not usually the users choice. The & seems to be used more than the CONCAT function as it’s more like entering regular formulas.
This page is showing the very basic usage in joining (concatenation) cells. Two additional methods are:
The Flash Fill feature and
The TEXTJOIN function which is a newer function that is much more robust than the older tools on this page.
Concatenating with Numbers
Concatenation works well with text but has issues with numbers. Once way to think about it is these features “add” text together. There are times when joining both text and numbers is needed. Using the TEXT function solves this problem.
Example:
Let’s say you have a sales report with many rows of data showing Units Sold and the Dollar Amount sold with totals for both columns and you desire to have a narrative statement at the bottom of the page that would look like:
Total units sold were 842 with total sales of $71,114. The formula would look something like:
=”Total units sold were “&D112&” with total sales of “&TEXT(F112,”$0,0”)
The TEXT function allows for the display of $71,114 rather than 71114. This more important if a date is to be displayed. This portion of the joined cells would be:
TEXT(A2,”mmmm d”)) to have it display something like April 23 rather than 45039.
Summary
This page is designed to show how to join text from cells using three different, yet very similar features. Joining/concatenating text is a very powerful set of tools. The next step up from these example can be found with using the Flash Fill feature and the newer TEXTJOIN Function. The Concat.xlsx file further explains the features along with exercises to help in the full understanding of the tools.
TextJoin.xlsx