Combine Text in Excel (8 Suitable Ways)

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.

Use of Ampersand (&) to Combine Text in Excel

The required formula in the output Cell B8 will be:

=B5&" "&C5&" "&D5

Use of Ampersand (&) to Combine Text in Excel

After pressing Enter, you’ll see the combined text as shown in the picture below.

Use of Ampersand (&) to Combine Text in Excel


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)

Or,

=CONCATENATE(B5," ",C5," ",D5)

Apply CONCATENATE or CONCAT Function to Join Text in Excel

Now press Enter and you’ll find the concatenated text in the output cell right away.

Apply CONCATENATE or CONCAT Function to Join Text in Excel


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:

=TEXTJOIN(" ",TRUE,B5,C5,D5)

Insert TEXTJOIN Function to Combine Text in Excel

After pressing Enter, you’ll get the combined text as shown in the following screenshot.

Insert TEXTJOIN Function to Combine Text in Excel


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.

Use of Power Query to Concatenate Text from Columns in Excel

📌 Step 1:

➤ Go to the Data tab or ribbon first.

➤ Click on the From Table/Range command.

A dialogue box will open up.

Use of Power Query to Concatenate Text from Columns in Excel

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

Use of Power Query to Concatenate Text from Columns in Excel

Now the Power Query Editor window will appear where your selected table data will show up.

Use of Power Query to Concatenate Text from Columns in Excel

📌 Step 3:

➤ Under the Add Column tab, select the option ‘Merge Columns’.

Use of Power Query to Concatenate Text from Columns in Excel

📌 Step 4:

➤ Choose the option ‘Space’ as Separator.

➤ Edit the new column name, for example, I’ve named it ‘Combined Data’.

➤ Press OK.

Use of Power Query to Concatenate Text from Columns in Excel

Now you’ll find the new column on the right with the header defined as ‘Combined Data’.

Use of Power Query to Concatenate Text from Columns in Excel

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

Use of Power Query to Concatenate Text from Columns in Excel

Like in the following picture, you’ll find a new table with the combined texts in a separate column.

Use of Power Query to Concatenate Text from Columns in Excel


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:

=B5&CHAR(10)&C5&" "&D5

Combine Text with Line Break in Excel

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.

Combine Text with Line Break in Excel


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.

Merge Text with Conditions in Excel

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:

=TEXTJOIN(CHAR(10),TRUE,IF(C5:C11=E5,B5:B11,""))

Here, the IF function has been used to input the condition or criteria.

Merge Text with Conditions in Excel

After pressing Enter, we’ll find the employee names with the line breaks for the specified department as shown in the following picture.

Merge Text with Conditions in Excel


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.

Combine Text by Using Fill Justify Option

📌 Steps:

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

Combine Text by Using Fill Justify Option

And you’ll see the combined text as the output in the first cell at once.

Combine Text by Using Fill Justify Option


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:

📌 Steps:

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

Use of VBA Codes to Combine Text in Excel

And in the output Cell B8, you’ll find the combined text at once.

Use of VBA Codes to Combine Text in Excel


Concluding Words

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.


You May Also Like to Explore

Lookup and Return Multiple Values Concatenated into One Cell in Excel

How to Concatenate Multiple Cells in Excel

How to Concatenate in Excel (3 Suitable Ways)

How to Use CONCATENATE Function in Excel (4 Examples)

How to Concatenate Two Columns In Excel ( 5 Simple Methods)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo