How to Use the CONCATENATE and CONCAT Functions to Join Text in Excel

Do you need to combine text strings in Excel? Learn how to use the CONCATENATE and CONCAT functions in this quick guide.

When working with data in Microsoft Excel, it's often necessary to combine text from multiple cells to create a well-organized and comprehensive dataset. Luckily, several functions are available for this purpose, including CONCATENATE and CONCAT.

Both CONCATENATE and CONCAT are straightforward functions that let you merge various values and strings in Excel. Although they're similar, there is one key difference between the two. Let's take a closer look at each function and determine which is best suited for different scenarios.

The CONCATENATE function in Excel joins multiple strings into a single cell. It allows you to merge two or more cell values, constants, or text strings. The syntax of CONCATENATE is as follows:

Here, text1, text2, text3, and so on represent the cell references or text strings you want to combine. You can include up to 255 text arguments in the CONCATENATE function. If you're working with text strings, put the strings in quote signs (" ").

When combining text strings and numerical values, it's important to include spaces between the different arguments to avoid confusion in the formula results. You can put spaces, punctuation marks, and symbols in the text strings.

This is a shared disadvantage of CONCATENATE and CONCAT compared to the TEXTJOIN function, as TEXTJOIN can automatically separate values with specific delimiters, such as commas or tabs.

A simple use of the CONCATENATE function is to take in the clients' first, middle, and last names and output the full name. For instance, in the worksheet above, the formula below returns the full names:

In this formula, CONCATENATE takes the names in A2, B2, and C2 and joins them together. Keep in mind that CONCATENATE doesn't support delimiters. Therefore, to avoid the names running together, include a space after each name in the original cell.

Another usage of CONCATENATE is summarizing information from different cells. Say you've got a worksheet with information about various items. You can combine the info about each item and conclude the outcome using CONCATENATE.

Consider the worksheet above. Here's a list of some products, their prices, the number of units sold, the sales target, the product's tag, and the status regarding its sales target. You can summarize all this data into a single sentence using the CONCATENATE function.

This lengthy formula is essentially simple. Every argument is a piece of the complete sentence. The CONCATENATE function joins the values from different cells and adds some text between them to make a sentence.

You can then hide the original columns to make your spreadsheet less crowded.

Available for Excel 2019 and later versions, the CONCAT function serves as the improved version of CONCATENATE. The syntax of the CONCAT function is as follows:

Like CONCATENATE, text1, text2, text3, etc., represent the cell references or text strings to be joined. The key advantage of CONCAT is its ability to handle ranges and arrays more efficiently.

Unlike CONCATENATE, the CONCAT function does not require explicit cell references for each argument. Instead, you can specify a range of cells or an array directly. This significantly reduces the formula's length and complexity when dealing with larger datasets.

In the same example as before, to combine the first, middle, and last names of clients in an Excel worksheet, the formula would be:

Like the CONCATENATE function, this formula takes the name values in A2:C2 and joins them together to display the full name. The difference here is that you can specify the range of values rather than separately referring to each. This is better emphasized in the worksheet below.

This sample worksheet belongs to an imaginary car manufacturer. Each car has a model code that contains information about various aspects of the vehicle. The model code comprises different codes. You can use the CONCAT function to swiftly combine the codes to form the model code in one short formula:

The CONCAT function takes the values in B1:B7 and joins them together to form the model code for each car. The same results with CONCATENATE would require the longer formula below:

When using an older version of Excel, CONCATENATE is a reliable choice for merging text values. However, even then, the ampersand (&) symbol can easily achieve the same results as CONCATENATE. Indeed, the formulas below all produce the same results:

One of the shortcomings of CONCATENATE is that it lacks unique functionalities. As demonstrated, the ampersand can do the same as CONCATENATE. Another issue is that the function's name itself is too long. Compare the ampersand formula with the CONCATENATE one to see for yourself.

To address these limitations, Excel introduced CONCAT. The CONCAT function can do everything CONCATENATE does, but it can also work with ranges and arrays. This makes CONCAT superior to CONCATENATE and the ampersand. As a result, if your version of Excel supports CONCAT, it's better to use CONCAT rather than CONCATENATE.

CONCAT's shorter name and ability to operate on ranges make the formulas shorter and easier to navigate. However, if your goal is to combine text strings, both CONCAT and CONCATENATE can be difficult to work with since they don't support delimiters. The TEXTJOIN function is a better option for merging text strings

Microsoft intends to replace CONCATENATE with CONCAT, so it's wise to get used to working with CONCAT. Although Excel will keep supporting the outdated CONCATENATE for compatibility with older Excel files.

Ironically, the CONCATENATE function in Google Sheets can operate on ranges and arrays; as a result, it's exactly identical to CONCAT in Google Sheets. The sole purpose of its existence is compatibility with old Excel files, should you import your Excel files into Google Sheets.

CONCATENATE and CONCAT are two Excel functions that let you join text strings and create cohesive datasets. You can use these functions to merge cell values, constants, or text strings and summarize information from different cells.

The CONCAT function can handle ranges and arrays more efficiently, and it has a shorter syntax than CONCATENATE, making it the preferred function for most users. However, if you are using an older version of Excel, CONCATENATE is still a reliable choice if you don't want to use the ampersand to join values.

Whichever function you choose, joining cells is an efficient way to summarize your data or produce new conjoined values. Combining cells can help you save space in your worksheet and make it easier to draw conclusions by introducing text strings.

Amir is a writer and junior editor at MUO. His love for writing and an affinity for organizing information took him to primarily write in the Productivity vertical. He enjoys driving, listening to music, and gaming in his free time. Amir has been writing tech articles for over 4 years and is currently pursuing a PharmD.