In Microsoft Excel, numerous methods are available to combine text with different criteria. In this article, you’ll get to learn all of those quick and simple methods to combine text with suitable examples and proper illustrations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
8 Suitable Approaches to Combine Text in Excel
1. Use of Ampersand (&) to Combine Text in Excel
In the following picture, there are some texts lying in B5, C5, and D5. We’ll combine these texts by using Ampersand (&) and the delimiter here will be space to separate all texts from each other.
The required formula in the output Cell B8 will be:
=B5&" "&C5&" "&D5
After pressing Enter, you’ll see the combined text as shown in the picture below.
2. Apply CONCATENATE or CONCAT Function to Join Text in Excel
We can also use the CONCATENATE or CONCAT function to combine texts in Excel. While using the CONCAT function, we don’t have to use Ampersand (&) anymore. Rather, we have to use commas (,) only to input all text arguments in the function.
So, the required formula with the CONCAT or CONCATENATE function will be:
=CONCAT(B5," ",C5," ",D5)
=CONCATENATE(B5," ",C5," ",D5)
Now press Enter and you’ll find the concatenated text in the output cell right away.
3. Insert TEXTJOIN Function to Combine Text in Excel
If you’re using Excel 2019 or Excel 365 then you can use the TEXTJOIN function to combine texts. In the TEXTJOIN function, you have to simply define a common delimiter and then specify the text values as arguments with commas (,).
The required formula with the TEXTJOIN function in the output Cell C8 will be:
After pressing Enter, you’ll get the combined text as shown in the following screenshot.
4. Use of Power Query to Concatenate Text from Columns in Excel
Now we have multiple text data in the following table. And what we have to do is combine texts alongside from each row and then show the outputs in an additional column on the right.
We can use any of the methods mentioned so far before this section but here we’ll use the Power Query method to concatenate text data.
📌 Step 1:
➤ Go to the Data tab or ribbon first.
➤ Click on the From Table/Range command.
A dialogue box will open up.
📌 Step 2:
➤ Enable editing in the Data Location box and select the entire table range (B4:D10).
➤ Put a mark beside the option “My table has headers”.
➤ Press OK.
Now the Power Query Editor window will appear where your selected table data will show up.
📌 Step 3:
➤ Under the Add Column tab, select the option ‘Merge Columns’.
📌 Step 4:
➤ Choose the option ‘Space’ as Separator.
➤ Edit the new column name, for example, I’ve named it ‘Combined Data’.
➤ Press OK.
Now you’ll find the new column on the right with the header defined as ‘Combined Data’.
📌 Step 5:
➤ Now it’s time to export the combined texts to the Excel spreadsheet. So, go to the Home tab in the Power Query Editor.
➤ Select the option ‘Close & Load’ and you’re done.
Like in the following picture, you’ll find a new table with the combined texts in a separate column.
5. Combine Text with Line Break in Excel
By inserting the CHAR function with a specific Unicode character, we can add a line break while combining texts. But before applying the formula, we have to customize the output cell a bit.
In the following picture, our output cell is B8 but we have to merge the cells B8 and B9 first. Then we have to enable text wrapping from the Alignment group of commands under the Home ribbon.
Now, we can insert the following formula in the output cell:
After pressing Enter, we’ll find the following result where the ID number is on the top and the full name is under the ID number. Here, the CHAR function with the Unicode character ‘10’ has added the line break.
6. Merge Text with Conditions in Excel
In the following dataset, we have now two columns with some random names of the employees and their corresponding departments in an organization. What we’ll do is extract the names of the employees for a specified department and then show the names successively with line breaks in Column F.
Assuming that we’re going to combine the employee names from the Sales department. So, the required formula in the output Cell F5 will be:
Here, the IF function has been used to input the condition or criteria.
After pressing Enter, we’ll find the employee names with the line breaks for the specified department as shown in the following picture.
7. Combine Text by Using Fill Justify Option
The use of the Fill Justify option is really effective when the command is able to find the pattern of a complete sentence from a number of sequential text data. In the following picture, some texts are lying within the range between B4 and B7. We’ll apply the Fill Justify option here to find out how this command works out to concatenate the selected texts.
➤ Select the range of cells (B4:B7) occupying the text data that have to be combined.
➤ Under the Home tab, select the Justify command from the Fill drop-down in the Editing group of commands.
And you’ll see the combined text as the output in the first cell at once.
8. Use of VBA Codes to Combine Text in Excel
In the last section, we’ll apply the VBA codes to combine text data. So, let’s go through the followings procedures to concatenate texts from B5, C5, and D5:
➤ Put your mouse cursor on the Sheet name and right-click the button.
➤ Select the option View Codes.
➤ In the VBA window, paste the following codes:
Option Explicit Sub ConcatenateText() Dim text As Range Dim i As String For Each text In Selection i = i & text & " " Next text Range("B8").Value = Trim(i) End Sub
➤ Now go back to your Excel spreadsheet and select the range of cells (B5:D5) that have to be concatenated.
➤ Open the VBA window again and press F5.
And in the output Cell B8, you’ll find the combined text at once.
I hope all of the formulas and procedures mentioned above will now help you to apply them in your Excel spreadsheets when you have to combine text data. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.