Excel CONCATENATE Function

Excel CONCATENATE function is used to join two or more text strings into one string. It is a useful function for creating full names, addresses, or any other text combination required in the spreadsheet.

Excel CONCATENATE Function Summary

Joining two or more text strings in Excel is made easy with the CONCATENATE function, which is a part of the text functions category. However, for users of Excel 2016, Excel Mobile, and Excel for the web, the CONCATENATE function has been replaced with the more versatile CONCAT function.

CONCATENATE Function Purpose

The purpose of the CONCATENATE function is to combine two or more strings of text into one. It is an efficient way to combine values without needing to insert spaces or other characters manually.

CONCATENATE Function Arguments

The CONCATENATE function requires two or more arguments, which can be text strings, cell references, or a combination of both.

CONCATENATE Function Return Value

The return value of the CONCATENATE function is a text string that results from combining the specified values.

CONCATENATE Function Syntax

=CONCATENATE(text1, [text2], …)

CONCATENATE Function Examples

In this section, we will explore various examples of how to use the CONCATENATE function to combine text strings with different functions and formulas in Excel. Whether you are working with dates, times, or numerical data, the CONCATENATE function can help you create powerful and dynamic text strings for your spreadsheets.

Creating a Dynamic Text String with CONCATENATE Function

Say we want to create a dynamic text string that displays today’s date and whether it is a weekday or weekend. We will use the TEXT and WEEKDAY functions in conjunction with the CONCATENATE function to achieve this.

=CONCATENATE("Today is ",TEXT(TODAY(),"mm/dd/yyyy")," and it is a ",IF(WEEKDAY(TODAY(),2)<6,"Weekday","Weekend"),".")

Let’s break down the function and see what’s happening:

• “Today is “: This is a text string that will be concatenated with the other strings and functions.
• TEXT(TODAY(),”mm/dd/yyyy”): This function returns the current date in the specified format of “mm/dd/yyyy”. The TODAY() function returns the current date, and the TEXT function formats it as a text string.
• ” and it is a “: Another text string to be concatenated.
• IF(WEEKDAY(TODAY(),2)<6,”Weekday”,”Weekend”): This function checks if today’s weekday number is less than 6 (Monday-Friday), and returns “Weekday” if true, or “Weekend” if false.
• “.”: A period to end the sentence.

So, when you put all of these elements together, the CONCATENATE function will return a string that tells you what today’s date is, and whether it’s a weekday or weekend. As you can see in our example, today is March 31, 2023 (a Friday), and returns: “Today is 03/31/2023 and it is a Weekday.”

Concatenating values with a custom delimiter using CONCATENATE

Say you want to concatenate a list of words with a hyphen (-) as the delimiter.

Assuming cell A2 contains “John”, B2 contains “Doe”, and C2 contains “1234”, the result will be “John-Doe-1234”.

To achieve this we need the following formula:

=CONCATENATE(A2,"-",B2,"-",C2)

This example concatenates the values in cells A2, B2, and C2 with hyphens in between.

Combining First and Last Names

Let’s say we have a first name in cell A2 and the last name in cell B2 and we want to separated them by a space. Resulting in a full name in a single cell.

Assuming cell A2 contains “John” and B2 contains “Doe”, the result will be “John Doe”.

To achieve this we need the following formula:

=CONCATENATE(A2," ",B2)

This example concatenates the values in cells A2 and B2 with a space in between.

Creating a Text String with Concatenation using CONCATENATE function

Let’s say we have text in different cells and we want to create a sentence that describes what someone loves to eat for breakfast.

Let’s say cell A2 contains “John” and B2 contains “pancakes”, and we want to create a text string “John loves to eat pancakes for breakfast.”

To achieve this we need the following formula:

=CONCATENATE(A2," loves to eat ",B2," for breakfast.")

Let’s break down the function and see what’s happening:

• A2: This is the cell reference for the name of a person.
• ” loves to eat “: This is a text string that is being used to join the values of A2 and B2.
• B2: This is the cell reference for a type of food.
• ” for breakfast.”: This is another text string that is being used to complete the sentence.

CONCATENATE Function Notes

• The ampersand operator “&” can also be used instead of the CONCATENATE function.
• The CONCAT function is a newer version of CONCATENATE and works in the same way.