Updated July 1, 2023
What is CONCATENATE in Excel?
CONCATENATE in Excel is an essential function that allows users to combine data from different cells and display the result in a single cell. For instance, if you have a list of addresses with the name, street name, city, etc., in different columns, you could use the CONCATENATE function to combine each section into a complete address.
You can then copy the combined address or use it elsewhere.
The CONCATENATE in Excel function is helpful for those who work with large volumes of data and want to combine the values of different columns into one. The utility of the function extends from connecting first and last names to linking data sections for generating unique codes.
Key Highlights
- The function CONCATENATE in Excel can combine a maximum of 30 values.
- This function always gives a text string, even if the source contains all numbers.
- To work correctly, we must provide at least one text argument in the formula for the CONCATENATE function.
- It gives a #N/A error if we provide an invalid value to an argument.
- The function CONCATENATE in Excel combines the source values and displays the result in a new cell. Therefore, it does not alter the source giving users the flexibility to work on new and old values.
Syntax of CONCATENATE in Excel
The syntax for CONCATENATE function is as follows-
The CONCATENATE function takes the following arguments:
Text 1: It’s a required argument and the first item to join. It can be a text value, cell reference, or number.
Text 2: It’s also a required argument. We can join up to 255 items that are up to 8192 characters.
How to Use CONCATENATE Function in Excel?
1. Using the Formula
Suppose we have a list of Names in Column A and a list of Dates in Column B and want to display the Name & Date together in Column C.
Here’s how we can do it:
Step 1: Open a new Excel worksheet and enter the list of names in Column A and the list of Dates in Column B.
Step 2: Give Column C a heading “Name & Date”
Step 3: In the cell C6, enter the formula =CONCATENATE(A6,” “,TEXT(B6,”DD-MM-YYYY”))
Explanation of the formula:
- A6: This is the first value we want to combine.
- “: To insert a space between the combined values, we enclose it in double quotes.
- TEXT(B6, “DD-MM-YYYY” ): The TEXT formula converts the number into a readable format. B6 represents the date, and DD-MM-YYYY is the format to display the same.
Step 4: Press the “Enter” key to display the combined text in the cell where you entered the formula.
Step 5: To concatenate the Name & Dates of the remaining cells, drag the formula into the other cells, and Excel will combine the values for all the cells.
2. Using the Function “fx.”
The table below lists the Material Code, Color Code, and Serial No. of the product MUG in different columns. We want to combine Material Code, Color Code, and Serial No into a single product code using the CONCATENATE function in Excel.
Let’s see how we can do this-
Step 1: Create a new column with the heading Product Code.
Step 2: Select the cell where you want to display the combined result. In this case, it is cell E6.
Step 3: Click on the Insert Function (fx) button as shown below
An Insert Function dialog box will appear.
Step 4: Type the function name you want to use in the Search for a Function section. Here, it is “Concatenate”, and click Go.
A list of functions will appear under the Select a Function section.
Step 5: Select Concatenate and click OK.
Excel will display a Function Arguments dialog box with Text fields or arguments.
Step 6: Select the cells one by one in the fields to combine them. Text 1 will be the first cell to combine; Text 2 will be the second to combine, and so on.
The combined value (in this case, product code) will appear in the dialog box, as shown in the image below.
Step 7: Click OK.
The function will display the concatenated value, in this case, Product Code, as shown below.
To combine the values of the remaining cells and create a product code, drag the formula in the remaining cells, and Excel will create different product codes, as shown below.
3. Using Ampersand Operator (&)
Suppose we have a list of Employee names in Column A and their Email IDs in column B. We want to concatenate these values to create a list of Employee Emails with their Display name using the Ampersand Operator (&).
Step 1: Create a new column heading “Email with display name“.
Step 2: In an empty cell (in this case, C6), enter the formula =A6&” <” &B6&”>”
How to write the formula:
Put the “=” sign and select the first cell to combine (A6). Enter the & operator and open double quotes. Put a space and open angle brackets. Enclose the second value (B6) between ampersand operators and double quotes. And lastly, end the formula with double quotes. The double quotes indicate that we want the output in text format.
Step 3: Press Enter key to get the combined result
To concatenate the Email ID with the Names of the remaining cells, drag the formula into the other cells.
4. Combine Text String and Cell Value Using CONCATENATE Function
Consider you have the text “I AM” written in one cell and “LEARNING” in another cell. We want to display the line- I AM LEARNING MICROSOFT EXCEL in a third cell. Follow the steps to see how we can use the Concatenate function to display this output.
Step 1: Create a new column heading “Result”.
Step 2: In an empty cell, let’s say C6, enter the formula =CONCATENATE(A6,” “, B6,” “, “MICROSOFT EXCEL”)
Explanation of the formula
- A6: The cell contains the first value we want to combine.
- “: A space enclosed with double quotes to separate the combined values with space.
- B6: We want to combine the cell containing the second value.
- “MICROSOFT EXCEL”: It is the third value to combine. It is enclosed in double quotes to indicate that we want to display a text value.
Step 3: Press Enter key to get the desired output
Things to Remember
- Stored in Text Format: Microsoft Excel stores the concatenated values in the form of text; thus, we cannot use it for calculations. To change the formatting or convert it into a number, right-click on the cell and go to Format Cells. Select the Number tab > Number > OK in the Format Cells dialog box.
- Same Order: The CONCATENATE function combines the values in the same order as in the data set. For example, if a column contains the alphabets in random order, i.e., A, C, D, F, and H, then the formula =CONCATENATE(A, C, D, F, H) will display it as
- Manual Cell Reference: If you want to combine the values of a cell range, you cannot use an array (such as B1:B10). You must refer to each cell manually, e.g., =CONCATENATE(B1, B2, B3,..).
- CONCAT function: In EXCEL 2016 and later versions of Excel, the CONCAT function in Excel has replaced the CONCATENATE function. However, it is still available to use for compatibility.
Frequently Asked Questions (FAQs)
Q1. What is concatenate in Excel?
Answer: Concatenate is an Excel function that lets users link two or more words or text strings together. The data can be text strings, cell references, or numbers. For instance, consider you have an Excel file with a long list of first and last names of clients written in different columns, and you want to display their full names in one column. While you can do it manually, the CONCATENATE function combines the values in seconds.
Q2. What is the shortcut for CONCATENATE?
Answer: The shortcut for CONCATENATE uses the ampersand operator (&). To combine values using the (&) operator, follow the steps:
Step 1: Place your cursor in the cell where you want the combined output
Step 2: Type the formula,
Step 3: Press Enter key to get the output
Q3. What is the advantage of CONCATENATE?
Answer: Working with numerical data in Excel is relatively easy to manipulate, but combining or manipulating text can be challenging. This is where the CONCATENATE function comes in handy. This function allows users to merge text strings without changing the original values, making it an ideal option for financial reporting or presentations.
Unlike merging cells, the CONCATENATE function does not alter the original values. It also allows for combining different data types, such as text strings, numbers, and dates, which is beneficial for data analysis and presentation.
Q4. What is concatenate in an Excel formula?
The CONCATENATE formula is =CONCATENATE(text1, text2, text3,…), where,
text1, text2, text3,… are the values we want to combine. We can combine up to 255 strings and 8192 characters in one formula.
Recommended Articles
The above article is a guide to CONCATENATE in Excel using different methods and downloadable templates. To learn more about such useful functions of Excel, you can read the following articles.
