EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources TEXT and String Functions in Excel CONCATENATE Function in Excel
 

CONCATENATE Function in Excel

Madhuri Thakur
Article byMadhuri Thakur

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.

 

 

CONCATENATE in Excel

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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-

CONCATENATE Formula syntax

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?

You can download this CONCATENATE in Excel Template here – CONCATENATE in Excel Template

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.
Use CONCATENATE Function step 1

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.

Use CONCATENATE Function step 3

  • 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.
Note: Excel will display the output in a non-readable format if we concatenate a date with text without converting it.

CONCATENATE Function

Step 4:  Press the “Enter” key to display the combined text in the cell where you entered the formula.
Use CONCATENATE Function step 4

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.
Use CONCATENATE Function step 5

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-
Using the Function “fx”

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

Insert Function (fx) step 3

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.
Insert Function (fx) step 4

A list of functions will appear under the Select a Function section.
Step 5: Select Concatenate and click OK.
Insert Function (fx) step 5

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.
Insert Function (fx) step 7

The function will display the concatenated value, in this case, Product Code, as shown below.
Insert Function (fx)

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.
Insert Function (fx) final

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“.
Using Ampersand Operator (&)

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.

Using Ampersand Operator (&) formula

Step 3: Press Enter key to get the combined result

Using Ampersand Operator (&) step 3

To concatenate the Email ID with the Names of the remaining cells, drag the formula into the other cells.

Using Ampersand Operator (&) step 3.1

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”.
Combine Text String and Cell Value Using CONCATENATE Function

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.

Combine Text String and Cell Value formula

Step 3: Press Enter key to get the desired output

Combine Text String and Cell Value step

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.

Things to Remember

Things to Remember1

  • 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.
What is concatenate in excel?

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,

=A6&” “&B6

Step 3: Press Enter key to get the output
What is the shortcut for CONCATENATE?

Note: To separate the values with space, we enclose space with double quotes in the formula.

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.

  1. VBA Concatenate
  2. VLOOKUP Function in EXCEL
  3. Multiple IFS in Excel
  4. Excel Percentage Difference

 

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download CONCATENATE in Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download CONCATENATE in Excel Template

EDUCBA

डाउनलोड CONCATENATE in Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW