![How to Use Concatenate in Excel [with Syntax and Examples]](../../ice9/free_resources_article_thumb/concatenate_in_excel.jpg "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.
Concatenate in Excel (Best practice)
How to combine data using the CONCAT function (Best Method)
- Choose a cell for the combined data to be placed.
- Please type =CONCAT(.
- Choose the cell you would like to combine first.
- Add spaces, commas, or other text. Use quotation marks to separate the cells you are combining.
- Enter the formula within parenthesis. =CONCAT(A2, " Family").
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], ...)
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)
Develop Your Career in Data Analytics with Purdue
Free Webinar | 30 March, Thursday | 9 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"))
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.
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).
Learn how to tackle complex business problems using in-demand tools with our Purdue PCP Business Analysis course. Sign-up 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 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!
