Asif Khan Pranto
In this article, we will learn How to Combine Two Cells In Excel. In Excel when data are Split across different cells, one can easily Combine them Automatically using different Formulas & Methods. There is no need to Combine them Separately. Your data might be in a nearby Column or Row, or it could be on a far off Cell. Reading this article we will be able to Combine Text from different Cells very easily.
Here we have the First Name & Last Name of Six French writers & poets in Column B & C respectively. We want to Combine the First Name & Last Name in Column D or Full Name Column. Now I will show you how to solve the problem stated above using several Formulas & Methods. Solving the problem we will learn how to Combine Two Cells In Excel.
Download Practice Workbook
6 Methods to Combine Two Cells in Excel
Method 1. Combine Two Cells in Excel with the Ampersand (&) Symbol
In this method, we will get to know how to Combine Two Cells in Excel using the Ampersand (&) Symbol. In Excel ‘&’ Symbol is called Ampersand instead of And. We will learn different methods applying Ampersand to Combine Cells without Space & with Space, Separator, Text & Number.
1.1. Combine Two Cells Using the Ampersand (&) Symbol Without Space
We will use this method when we do not require any Space among Data of different Cells. Here we want to Combine the First Name Column and Last Name Column without any Space in the Full Name Column.
Step 1:
- First, we have to select a Cell from the Column where we want to Combine our data.
- I have Selected Cell D5 of the Full Name Column.
- Here I want to Combine Cell B5 and Cell C5 without any Space and to execute this I have typed the following Formula in cell D5.
- The Formula is
=B5&C5
Step 2:
- Upon Pressing ENTER we will see that Cell B5 and Cell C5 have Combined to be ‘FranzKafka’ in Cell D5 without any Space.
Step 3:
- Now we can use the Fill handle to AutoFill the Formula for the rest of the Cells of the Full Name Column.
1.2. Using the Ampersand (&) Symbol to Combine Cells with Space/Separator/Text & Number
Here we will learn how to Combine Two Cells using the Ampersand (&) Symbol having Space, Separator, Text & Numbers.
Step 1:
- Here we want to Combine Cell B5, C5 & D5 by inserting a few Spaces, Separators & Texts among them.
- In Cell E5 we want it to look like ‘Franz+Space+Kafka+Space+was born at+:+03-07-1883’.
- The Formula in Cell E5 will be
=B5&" "&C5&" "&"was born at"&":"&D5- If we find difficulties using Numbers, using the TEXT Function can solve it.
Step 2:
- Now upon pressing ENTER we will find our desired output in Cell E5.
Step 3:
- Now we will use the Fill handle to AutoFill the Formula for the rest of the Cells of the Full Name & Birthdate Column instead of doing it manually.
Read More: How to Combine Names in Excel with Space (6 Approaches)
Method 2. Applying the CONCAT Function to Combine Cells
In this method of the following article, we will see how to Combine Two Cells using the CONCAT Function with or without Space. Only the latest versions of Excel have this Function. Let’s have a look at those methods.
2.1. Combine Two Cells in Excel Using the CONCAT Function Having Space
Step 1:
- Here we want to Combine the First Name Column & the Last Name Column into the Full Name Column using the CONCAT Function.
- First Select any Cell within the Full Name Column. I have selected Cell D5 for Combining Cell B5 & C5.
- Now type the CONCAT formula in cell D5
=CONCAT(B5,C5)
Step 2:
- Now upon pressing ENTER we will find our desired output in Cell E5 which has used CONCAT Function.
Step 3:
- Now we will use the Fill handle to AutoFill the formula for the rest of the Cells of the Full Name Column instead of applying CONCAT Function for every cell.
2.2. Combine Two Cells in Excel Using the CONCAT Function Having Space, Text & Numbers
In this section, we will apply the CONCAT function to include Space, Text, Numbers & Separators while Combining Two or Cells.
Step 1:
- Here we want Combine Cell B5, C5 & D5 by inserting a few Spaces, Text & Numbers among them
- In Cell E5 we want it to look like ‘Franz+Space+Kafka+Space+died at:+40’.
- The Formula in Cell E5 will be
=CONCAT(B5," ",C5," ","died at",":",D5)- The Formula of CONCAT Function is quite similar to the Ampersand Formula.
Step 2:
- Now upon pressing ENTER we will find our desired output in Cell E5.
Step 3:
- Now we will use the Fill handle to AutoFill the Formula for the rest of the Cells of Full Name & Age at Death instead of doing it manually.
Method 3: Applying CONCATENATE Function to Combine Cells
Not every version of Excel has a CONCAT function. Instead, they have CONCATENATE Function. CONCAT function is the replacement of CONCATENATE Function in newer versions. One difference between them is that the CONCAT function can select a Range of Cells at once where CONCATENATE has to select every function individually.
Step 1:
- First, we have to select a Cell from the Column where we want to Combine our data.
- I have Selected Cell D5 of the Full Name Column.
- Here I want to Combine Cell B5 and Cell C5 and to execute this I have typed the following Formula in cell D5
- The CONCATENATE Formula is
=CONCATENATE(B5,C5)
Step 2:
- Upon Pressing ENTER we will see that Cell B5 and Cell C5 have Combined to be ‘FranzKafka’ in Cell D5
Step 3:
- Now we will use the Fill handle to AutoFill the formula for the rest of the Cells of Full Name Column instead of doing it by applying CONCATENATE Formula to every cell.
Read More: How to Combine Cells Using Excel Formula (6 Methods)
Method 4. Executing TEXTJOIN Function to Combine Cells
Newer version of Excel has introduced TEXTJOIN Function to Combine Two Cells. Now we will see how it works.
Step 1:
- At first select the Cell where we want to apply the TEXTJOIN Function. Here I have selected the D5 Cell where I want to Combine B5 & C5 Cells.
- Then we have to type the TEXTJOIN Formula. First we have to declare a Delimiter.
- Second argument is ‘ignore_empty’. So to ignore empty cells we will type True.
- Formula for D5 can be written as
=TEXTJOIN(“ “,True,”Franz”,”Kafka”)or
=TEXTJOIN(“ “,True,B5:C5)
Step 2:
- Now upon pressing ENTER we will find our desired output in Cell E5.
Step 3:
- Now following Step 2 for Full Name Column we get the dataset below.
Read More: How to Combine Cells in Excel (6 Methods + Shortcut)
Method 5. Using Flash Fill to Combine Two Cells
We can use the Flash Fill method to Combine Cells. It’s very easy to use & convenient.
Step 1:
- First type The First Name & Last Name of Row 5 in Cell D5.
Step 2:
- Now Select the Range of Cells where we want to Flash Fill.
- Now to Flash Fill Follow Home tab >> Editing >> Fill >> Flash Fill.
- Upon Clicking Flash Fill it will be Filled up Automatically.
- We can also use Flash Fill Shortcut CTRL + E.
Method 6. Combining Cells Using the Merge & Center
Merging Cells is a very easy method to Combine Two Cells. But the Merged Cell only takes the data of the Left-Top Cell & forget other Cell values. So we can use this method to Combine Two Cells only when there is no data in Adjacent cells.
Step 1:
- In the dataset below we want to Merge Cell B5 with Cell C5 & D5.
- In order to do that first we have to select those cells.
Step 2:
- Now to Merge those Cells we have to follow the Home tab >> Alignment >> Merge & Center.
- Upon checking Merge & Center we can select either Merge & Center or Merge Across.
- Here we have selected Merge & Center & the following dataset now looks perfect where Cells have been Combined.
Read More: Shortcut for Merge and Center in Excel (3 Examples)
Practice Section
Here I have provided you with a Practice sheet. Try it!
Conclusion
By using any of the above methods you will learn How to Combine Two Cells in Excel. Those methods are very easy & efficient. Hope you find this article useful. If you have any confusion, please leave a comment.
