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.
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.
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.
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.
Step-02: After pressing ENTER the texts will be merged as the following image.
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
Step-02: After pressing ENTER the following table will be formed.
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.
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.
Step-04: After pressing ENTER the texts will be merged as below.
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
Step-02: After pressing ENTER and dragging it down the following results will appear.
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.
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.
Step-04: After pressing ENTER the following result will appear.
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
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.
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.
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.
Step-05: After pressing OK a new column named Full Name will appear where two names are merged.
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.
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.
Step-03: After pressing ENTER and dragging down through the column Name & Email Id the texts will be merged with the desired line break.
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.