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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Merge Text from Two Cells Dataset


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.

=B5&C5

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

Excel Merge Text from Two Cells Using Ampersand Symbol (&)

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.

Excel Merge Text from Two Cells Using Ampersand Symbol (&)


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.

=B5&" "&C5

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.

=B5&", "&C5

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

=B5&"; "&C5

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

Excel Merge Text from Two Cells Using Ampersand Symbol (&)


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.

=CONCATENATE(B5," ",C5)

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.

Combine Text Using the CONCATENATE Function


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.

=CONCAT(B5," ",C5)

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

Excel Merge Text from Two Cells Using the CONCAT Function

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.

=CONCAT(B5:C5," ",B6:C6)

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

Excel Merge Text from Two Cells Using the CONCAT Function

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

Excel Merge Text from Two Cells Using the CONCAT Function


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

=B5&CHAR(10)&C5

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

Merge Text While Keeping Line Breaks

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

Then you’ll get the following output.

Merge Text While Keeping Line Breaks

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.

=CONCAT(B5," ",C5,CHAR(10),B6," ",C6)

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

Merge Text While Keeping Line Breaks

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

Merge Text While Keeping Line Breaks


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.

=TEXTJOIN(" ",FALSE,B5,C5)

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.

Excel Merge Text from Two Cells Utilizing TEXTJOIN Function

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.

=TEXTJOIN(" ",TRUE,IF($B$5:$B$13=E5,$C$5:$C$13," "))

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.

Excel Merge Text from Two Cells Utilizing TEXTJOIN Function

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.

Excel Merge Text from Two Cells Utilizing TEXTJOIN Function


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.

Combine Text Using Power Query

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.

Combine Text Using Power Query

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

Combine Text Using Power Query

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

Combine Text Using Power Query

Step 3: Loading the Output into Worksheets

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

Combine Text Using Power Query

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

Combine Text Using Power Query


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.

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

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

Merge Text Using VBA

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.

Merge Text Using VBA

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.

Merge Text Using VBA

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

Merge Text Using VBA

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

Merge Text Using VBA


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.


<< Go Back to Excel Concatenate Text | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo