How to Merge Text from Two Cells in Excel (7 Methods)

Sometimes we need to merge multiple cells into one to get the desired output. Undoubtedly, Excel provides some quickest features to do that. In this article, I’ll discuss the 7 quickest methods to merge text from two cells in Excel with the necessary explanation.

We’ll use the following dataset for our today’s tasks. Here, first name and last name are given. And, we need to merge text from these two cells.

1. Merging Text Using Ampersand Symbol (&)

At the outset, I’ll show you a simple method to merge two cells – using the ampersand symbol (&). We may use the symbol in two separate ways.

Ampersand Symbol without Separator

If you want to merge text from two cells excluding any space character that means without a separator, you may use the ampersand symbol as shown in the below formula.

Here, B5 is the starting cell of the first name and C5 is the starting cell of the last name.

After inserting the formula in the D5 cell, if you press Enter and use the Fill Handle Tool (just drag down the green-colored small square located at the right-bottom of the cell), you’ll get the following output.

Ampersand Symbol with Space Character

But we need space characters between the full names in this dataset. Also, you may need a space character to merge text from two cells. In such a situation, just use the following formula.

Here, I put space inside double quotes to include the space between the merged text.

If you need to use comma space, just input the comma instead of the space.

Again, you may use the semicolon space in place of a comma for your requirement.

After entering formulas and using the Fill Handle Tool, the output will be as follows.

Read More: How to Combine Multiple Columns into One Column in Excel

2. Combining Text Using the CONCATENATE Function

The CONCATENATE function combines multiple strings into a single string. Hence, we may use the function to merge text.

Here, B5 is the starting cell of the first name and C5 is the starting cell of the last name.

If you press Enter and use the Fill Handle Tool, you’ll get the following output.

3. Joining Text Using the CONCAT Function

As you know, Microsoft recommends the CONCAT function instead of using the CONCATENATE function. The CONCAT function also combines multiple strings into a single string, but it doesn’t have a default delimiter. But you can input the delimiter manually if you want.

If we want to get the full name from the two cells using the function, we have to use the following formula.

Here, B5 is the starting cell of the first name and C5 is the starting cell of the last name.

More importantly, the CONCAT function has a special feature as it can combine a range of cells.

If you need to combine a range of texts, you can utilize the following formula.

Here, B5 & C5 are the cells of the name but B6 & C6 are the cells for showing the belonging states name.

If you press Enter, and repeat inserting the formula for other cells, you’ll get the following output.

4. Merging Text While Keeping Line Breaks

In some cases, we need to keep line breaks between merged text to make it visually different.

For doing that we need to use the CHAR function which checks the character on the basis of a given number or code. The ASCII code for inserting a line break is 10, so we have to use CHAR(10) to embed a line break between the merged texts.

So the adjusted formula will be

Here, B5 is the starting cell of the first name and C5 is the starting cell of the last name.

Next, press Enter and use the Fill Handle tool to copy the formula for the below cells.

Then you’ll get the following output.

Interestingly, we also can use the CONCAT function to embed line breaks with giving space in-between texts.

So the formula will be as follows.

Here, B5 & C5 are the cells of the name but B6 & C6 are the cells for showing the belonging states name, CHAR(10) is for keeping a line break, and two spaces are used inside double quotes to include space between the merged text (e.g. the space between states and the name of states).

If you press Enter and use the same formula except changing the cell name, you’ll get the following output.

5. Merging Text from Two Cells Utilizing the TEXTJOIN Function

The TEXTJOIN function (available from Excel 2019) also joins multiple strings including a delimiter character.

Whatever, if we want to count empty cells while merging text, we have to choose FALSE in the case of the second argument. So the formula will be as follows.

Here, B5 is the starting cell of the first name and C5 is the starting cell of the last name.

After pressing Enter, and then using the Fill Handle tool, the output will be as follows.

Now I’ll show you a significant application of the TEXTJOIN function. In the previous example, we just merged cells without any condition. What if we have a condition while merging text?

Say, you are the CEO of a company, and you have the list of Leisure Time Work for each employee. But you need to list the works (if each employee does several works) for some particular employee.

Here, “ “ is the delimiter, and TRUE is used to ignore blank cells.

Besides, I used $B$5:$B$13=E5 as an array to assign the selected employee from the list of employees, and  $C$5:$C$13 to find the work for the selected employee.

As it is an array function, you must press CTRL + SHIFT + Enter to get the output. Next, use the Fill Handle Tool for copying the formula for the below cells.

6. Combining Text Using Power Query

Furthermore, you can use the Power Query tool to merge text from two cells in Excel quickly with higher efficiency.

The process of merging texts using the tool is described below in a step-by-step process.

Step 1: Inserting the Dataset into the Power Query Editor

For opening the Power Query Editor, you need to select the whole dataset and choose

⇰ From Table/Range from the Get & Transform Data ribbon.

⇰ If you see the Create Table dialog box, then press OK with checking the box before My Table has headers.

Step 2: Merging the Columns

Now you’re in the Power Query Editor.

⇰ Select the two columns by pressing SHIFT and click on the Merge Column from the Add Column tab.

Next, choose Separator as Space and type Full Name in the blank space under New Column name, and lastly press OK.

So, you’ll get the following output where the full name is found.

Step 3: Loading the Output into Worksheets

Finally, you need to export the output into your worksheets by clicking File > Close & Load.

Then you’ll see a dialog box where you want to export the data. If you choose the new worksheet, you’ll see the following output (also you may choose the existing worksheet).

7. Merging Text from Two Cells Using VBA

Lastly, if you want, you may utilize the VBA code for merging texts. Let’s follow the instructions below to use the VBA code to merge cells in Excel.

Step 1:

Firstly, open a module by clicking Developer > Visual Basic.

Secondly, go to Insert > Module.

Step 2:

Then copy the following code into the newly created module.

Sub MergeText_VBA()
    Dim SourceCells As Range
    Dim DestinationCell As Range

    Set SourceCells = Application.InputBox(prompt:="Select the cells to merge", Type:=8)
    Set DestinationCell = Application.InputBox(prompt:="Select the output cell", Type:=8)
    temp = ""
    For Each Rng In SourceCells
        temp = temp & Rng.Value & " "
    Next
    DestinationCell.Value = temp
End Sub

In the above code, I declared SourceCells and DestinationCell as Range type. Then I used InputBox for each item for selecting source and destination cells. Finally, I utilized the variable temp to keep the space by combining the space and Rng.Value function.

Next, if you run the code (the keyboard shortcut is F5 or Fn + F5), you’ll see the following dialog box where you have to fix the cells that you want to merge.

Simultaneously, you’ll see the following dialog box after pressing OK in the previous box. Choose the destination cell where you want to get the merged text.

Immediately, you’ll get the merged text as shown in the below.

Now, just repeat the process for the below cells and the output will be as follows.

Download Practice Workbook

Conclusion

Here, I discussed 7 methods to merge text from two cells in Excel. However, there are several other effective methods like Flash Fill to assist you. Anyway, if you have any queries or suggestions, please let them below.

Related Articles

• How to Concatenate Range in Excel
• How to Concatenate Rows in Excel
• How to Concatenate Rows in Excel with Comma
• How to Concatenate Two Columns in Excel
• How to Concatenate Two Columns in Excel with Hyphen