How to Use Concatenate in Excel [with Syntax and Examples]

The word CONCATENATE means to join or combine. The CONCATENATE function in Excel is used to combine the text from different cells into one cell. This Excel tutorial will explain how to use the Excel CONCATENATE function with syntax and examples.

In this article, we will discuss the following topics:

  • Excel CONCATENATE Function
    • Concatenating the Values of Several Cells
    • Concatenating a Text String and a Cell Value
    • Concatenating a Text String and Formula Calculated Value
  • Important Points to Remember
  • Concatenate Cells with other Characters
  • Concatenate Columns in Excel

Excel CONCATENATE Function

The Excel CONCATENATE function is used to join up to 30 text items and return the result as a text. 

The syntax of the Excel Concatenate function is as follows:

Syntax: =CONCATENATE (text1, text2, [text3], ...)

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

Concatenating the Values of Several Cells

The simple formula to CONCATENATE the values of two cells A2 and B2 are as follows:

=CONCATENATE(A2,B2) 

The values will be combined without any delimiters.

To separate the values with space, use “ “.

=CONCATENATE(A3, “ “, B3)

concat1

Concatenating a Text String and a Cell Value

The Excel CONCATENATE function is not only limited to joining cell value. You can use the function to join a cell value and a string as well. For example:

=CONCATENATE(A2, " ", B2, " SIMPLILEARN")

concat2

Concatenating a Text String and Formula Calculated Value

You can also concatenate a text string and a formula calculated value. 

For example, you can use the following formula to return the current date:

=CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy"))

concat3

Important Points To Remember

You should follow some rules to ensure that the CONCATENATE function always delivers the correct result.

  • There should be at least one “text” argument in the CONCATENATE function for it to work.
  • If the CONCATENATE function's arguments are invalid, the formula returns a #VALUE! Error.
  • The result of the CONCATENATE function is always a text string, even when all the source values are numbers.

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

Concatenate Cells with Space, Comma, and Other Characters

In the worksheet, you may require to join the values in a way that will include commas, spaces, or various punctuation marks. To do that, follow the syntax mentioned below.

Concatenating two cells with space:

=CONCATENATE(A2, “ “ ,B2)

Concatenating two cells with a comma:

=CONCATENATE(A3, “, ”, B3) 

Concatenating two cells with space:

=CONCATENATE(A4, “ - “, B4)

commas1-concatenate

Concatenate Columns In Excel

Concatenating the whole column at once is very easy. Just enter the formula in the first cell and then copy it down to the other cells by dragging the fill handle (the small square that appears at the lower right corner of the selected cells).

COLUMNS1

Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.

Conclusion

So, in this article, we discussed how to concatenate text in our Excel worksheet. We have seen several examples of the CONCATENATE function with different syntax.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

Do you have any questions? Feel free to ask them in this article’s comments section, and our experts will promptly answer them for you! 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.