The Excel CONCATENATE function is used to join two or more text items from multiple cells into one.
Text1 (Required): The first text item to be joined.
Text2 (Optional): The additional text item to be joined.
The Text arguments can be:
A text value
A cell reference
Return the joined text.
1. You can combine up to 255 text arguments at the same time. 2. The #NAME? Error occurs when including text in the function without double quotation marks.
This section provides CONCATENATE function examples to show you how to use it in Excel.
Example 1: Combine first name and last name
As the below screenshot shown, how to combine the first, middle and the last names into one cell? The CONCATENATE function will help to solve the problem.
1. Select a blank cell, copy the below formula into it and press the Enter key. And then drag the FillHandle down to get all full names. =CONCATENATE(B3,C3,D3)
Notes: You can see there is no delimiter in the full name. If you want to combine the texts and separate them by certain delimiter, such as space, apply the below formula. You can change the " " to "," or other delimiter as you need. =CONCATENATE(B3," ",C3," ",D3)
Example 2: Concatenate cells with line break (new line)
For most of times, you may need to concatenate cells with line breaks. This section will show the method to achieve it.
1. Select a blank cell, copy the below formula into it and press the Enter key, and then drag the FillHandle down to get all joined texts. =CONCATENATE(B3,CHAR(10),C3,CHAR(10),D3,CHAR(10),E3)
Note: CHAR(10) represents the line break in Excel.
2. Keep selecting all result cells, go to the Home tab, and click the Wrap Text button. See screenshot:
Then the joined texts are separated by line breaks.
More CONCATENATE articles:
Bold part text when concatenate two columns in Excel In Excel worksheet, after concatenating two cell values with formulas, you may want to bold part of the text in the combined formula cell. This article has the method to help you get it done. Click to know more...
Concatenate cell columns and keep text color in Excel As we all known, while concatenating or combining cell columns into one column, the cell formatting (such as text font color, number formatting, etc) will be lost. This article, I will introduce some tricks to combine the cell columns into one and keep the text color as easily as possible in Excel. Click to know more...
Concatenate cell values until if finds a blank cell in a column Supposing you have a list of values which including some blank cells, and now want to concatenate each set of data between blanks into a cell and list the combined values in another column. Try the method in this article. Click to know more...
Concatenate cells from another sheet/different sheets in Excel In general, we apply CONCATENATE function to merge cells from the same worksheet. But sometimes you may need to concatenate cells from different worksheets in Excel. This article shows two methods to help you get it done easily. Click to know more...
Concatenate range/cells with comma, space, line break in Excel This article is talking about concatenating a row/column into one cell with separating by comma or space, as well as concatenating a range into one row/ column/cell with separating each cell content by any kind of separators, such as comma, space, line break, etc. in Excel. Click to know more...
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
Designed for 1500 work scenarios, helps you solve 80% Excel problems.
Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.