How to Combine Two Cells in Excel (6 Quick Methods)

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

How to Combine Two Cells in Excel

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
Formula Explanation: To add different Cells & Outside values we need to use Ampersand (&) Symbol in between and to insert Space, Separator, Text or Number we have to put that in between Double Quotes (“x“), Where x= Space/Separator/Text/Number.
  • 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.

How to Combine Two Cells in Excel

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)

How to Combine Two Cells in Excel

Step 2:

  • Now upon pressing ENTER we will find our desired output in Cell E5 which has used CONCAT Function. 

How to Combine Two Cells in Excel

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)
Formula Explanation: To add different Cell values & outside values we need to use Comma (,) Symbol in between, and to insert Space, Separator, Text, or Number we have to put that in between Double Quotes (“x“), Where x= Space/Separator/Text/Number.
  • The Formula of CONCAT Function is quite similar to the Ampersand Formula.

How to Combine Two Cells in ExcelStep 2:

  • Now upon pressing ENTER we will find our desired output in Cell E5.

How to Combine Two Cells in ExcelStep 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)

How to Combine Two Cells in Excel

Step 2:

  • Upon Pressing ENTER we will see that Cell B5 and Cell C5 have Combined to be ‘FranzKafka’ in Cell D5 

How to Combine Two Cells in Excel

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)

How to Combine Two Cells in Excel

Step 2:

  • Now upon pressing ENTER we will find our desired output in Cell E5.

How to Combine Two Cells in Excel

Step 3:

  • Now following Step 2 for Full Name Column we get the dataset below.

How to Combine Two Cells in Excel

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.

How to Combine Two Cells in Excel

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.

How to Combine Two Cells in Excel

  • Upon Clicking Flash Fill it will be Filled up Automatically.
  • We can also use Flash Fill Shortcut CTRL + E.

How to Combine Two Cells in Excel


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.

How to Combine Two Cells in Excel

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.

How to Combine Two Cells in Excel

  • Here we have selected Merge & Center & the following dataset now looks perfect where Cells have been Combined.

How to Combine Two Cells in Excel

Read More: Shortcut for Merge and Center in Excel (3 Examples)


Practice Section

Here I have provided you with a Practice sheet. Try it!

How to Combine Two Cells in Excel


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.


Related Articles

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo