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

Sometimes we need to merge multiple cells into one for getting 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.


Download Practice Workbook


7 Methods to Merge Text from Two Cells in Excel

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


1.1. Ampersand Symbol without Separator

If you want to merge text from two cells excluding any space character that means without 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 (&)


1.2. Ampersand Symbol with Space Character

But we need space characters between the full name 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 (&)

Read More: How to Merge Cells in Excel Table (7 Ways)


2. Combine Text Using the CONCATENATE Function

The CONCATENATE function combines multiple strings to 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

Read More: How to Merge Text Cells in Excel (9 Simple Methods)


3. Join Text Using the CONCAT Function

As you know, Microsoft recommends the CONCAT function instead of using the CONCATENATE function. The CONCAT function also combines the 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

Read More: How to Merge Cells in Excel with Data(3 Ways)


4. Merge 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, 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


Similar Readings


5. Merge 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, 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

Read More: How to Merge Text from Two or More Cells into One Cell (Easiest 6 ways)


6. Combine Text Using Power Query

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

The process of merging texts using the tool is described below by 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. Merge Text from Two Cells Using VBA

Lastly, if you want, you may utilise the VBA code for merging texts.

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

Read More: VBA to Merge Cells in Excel


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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo