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

If you are looking for some simple and easiest ways to merge text from two or more cells into one cell, then you are in the right place. Merging texts can reduce time and extra work while it is needed to write it manually. Let’s get into the main article.

Download Excel Workbook


6 Ways to Merge Text from Two or More Cells into One Cell

To describe the methods of merging text from two or more cells into one cell I have taken the following dataset. Here, I have 5 columns and 8 rows. In the First Name and Last Name columns, I have some names. Using these names I will combine them using different methods. So, let’s start.

dataset


Method-1: Merge Two or More Cells Using Flash Fill Feature

Step-01: In the Full Name column I have to merge the first name and last name of a person. To do this in the first row of the Full Name column you have to write manually the full name at first using the first row of the First Name and Last Name column.

Flash fill feature

Step-02: Then when you start typing in the second row of the Full Name column the following suggestions will appear. This is called the Flash Fill feature of Excel.

Step-03: After pressing ENTER the texts will be merged as below.

merge multiple text from two or more cells into one cell


Method-2: Using Ampersand(&) Sign

Step-01: Ampersand sign merges different texts, spaces, etc. Here in the Email Id column ampersand sign will be used to merge First Name, Last Name, and a common text named “@gmail.com”.
We are going to use the following formula

=LOWER(B4&C4&"@gmail.com")

Here, & joins the texts

=LOWER(text)

This LOWER function will convert all letters in a text string to lowercase.

ampersand sign

Step-02: After pressing ENTER the texts will be merged as the following image.

merge multiple texts from two or more cells into one cell


Method-3: Using CONCATENATE Function to Merge Two or More Cells

Case 1: Merging texts of two columns
Step-01:  Here in the Email Id column the CONCATENATE function will be used to merge First Name, Last Name, and a common text named “@gmail.com”.

=CONCATENATE(text1,text2,text3….)

Here, text1= B4, text2= C4, text3=@gmail.com

concatenate function

Step-02: After pressing ENTER the following table will be formed.

merge texts from two or more cells into one cell

Case 2: Merging texts of two rows
Let’s consider another scenario now. Suppose two people have secured the same position in a competition.
Against Position, Combined Name is added.
The first two people in this table have secured 3rd position.
So, now you have to add two people’s names with a separator as a comma in the first row of the Combined Name column.

row wise combination of texts

Step-03: To do this you have to select the texts in the CONCATENATE function in both of the row-wise and column-wise directions as below.

=CONCATENATE(B4," ",C4,",",B5," ",C5)

Here, B4 and C4 are the first name and last name respectively of a person and they are separated by space.
And B5, C5 are the first name and last name of another person. These names are separated by a comma.

row wise combination of texts

Step-04: After pressing ENTER the texts will be merged as below.

row wise combination of texts


Method-4: Using TEXTJOIN Function

Case 1: Merging texts of two columns
Step-01: Here you have to use the TEXTJOIN function in the first row of the Full Name column, to merge the first row of the First Name and Last Name.

=TEXTJOIN(" ",TRUE,B4:C4)

Here, Space is a delimiter, TRUE is for ignore empty cells, the range for texts is B4:C4

textjoin function

Step-02: After pressing ENTER and dragging it down the following results will appear.

textjoin function

Case 2: Merging texts of two rows
Let’s consider another scenario, the same as Method-3.
Suppose two people have secured the same position in a competition.
Against Position, Combined Name is added. The first two people in this table have secured 3rd position.
So, now you have to add two people’s names with a separator as a comma in the first row of the Combined Name column.

row wise combination of texts

Step-03: To join the texts in the row-wise direction of the Full Name column TEXTJOIN Function is used here.

=TEXTJOIN(",",TRUE,D4,D5)

Here, the delimiter is a comma, TRUE is for ignore empty cells, and D4, D5 are the first two cells of the Full Name column.

row wise merge text from two or more cells into one cell

Step-04: After pressing ENTER the following result will appear.

row wise combination of texts

Read more: How to Merge and Center Cells in Excel (3 Easy Methods)


Method-5: Using Power Query to Merge Two or More Cells

Step-01: You can use Power Query to merge texts easily. To do this you have to follow Data Tab >> From Table/Range

power query

Step-02: After that Create Table dialog box will appear where you have to select the range to create a table and click on the option named My table has headers and press OK.

create table dialog box

Step-03: Then Power Query Editor will appear where you have to select the First Name and Last Name columns and then follow  Add Column>>Merge Columns.

power query editor for merge text from two or more cells into one cell

Step-04: Then the Merge Columns dialog box will appear where you can choose any type of Separator from the option and write any type of name in the New column name option.
Here, I have selected Comma as a Separator and used Full Name in the New column name option.

merge columns dialog box

Step-05: After pressing OK a new column named Full Name will appear where two names are merged.

merge text from two or more cells into one cell


Method-6: Merging Texts With Line Break

Step-01: Here, in the column, I want to merge Full Name and Email Id in the Name & Email Id column with a line break between name and email id. At first, you have to select the Wrap Text option.

wrap text

Step-02:Then use the TEXTJOIN function as below

=TEXTJOIN(CHAR(10),TRUE,B4,C4)
=CHAR(number)

Here, number=10, because it is used for a line break.
In the TEXTJOIN function, CHAR(10) is used as a delimiter, TRUE is for ignore empty cells and texts are B4, C4.

textjoin function

Step-03: After pressing ENTER and dragging down through the column Name & Email Id the texts will be merged with the desired line break.

merge text from two or more cells into one cell


Conclusion

In this article, I tried to cover some easiest ways to merge text from two or more cells into one cell quickly. Hope this article will help you a lot. If you have any further ideas related to this topic then you can share them with us. You can ask any question here. Thank you.


Further Readings:

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo