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

Excel CONCATENATE Function

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

The Excel Concatenate formula is as follows:

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

How to Combine Data Using the CONCAT Function 

1. Choose a cell for the combined data to be placed.
2. Please type =CONCAT(.
3. Choose the cell you would like to combine first.
4. Add spaces, commas, or other text. Use quotation marks to separate the cells you are combining.
5. Enter the formula within parenthesis.  =CONCAT(A2, " Family").

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)

Future-Proof Your AI/ML Career: Top Dos and Don'ts

Free Webinar | 5 Dec, Tuesday | 7 PM ISTRegister Now

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")

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"))

How to Combine Data With the Ampersand Symbol (&)

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.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

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)

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).

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 Professional Certificate Program In Business Analysis 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!