How to Combine Cells Using Excel Formula (6 Methods)

When dealing with Microsoft Excel, we occasionally need to combine cells with their values using Excel formulas. We can easily apply the Excel formula to combine cells. To combine cells in Excel, we can use CONCATENATE function, Ampersand Symbol, TEXTJOIN Function, CHAR formula, and so on.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Suitable Ways to Combine Cells Using Excel Formula

Let’s assume a scenario where we have information about the First Name and Last Name in Column B and Column D respectively. In our dataset, we’ll combine First Name and Last Name in Column D by using Excel formulas. Here’s an overview of the dataset of our today’s task.

Using the CONCATENATE Function to Combine Cells in Excel


1. Using the CONCATENATE Function to Combine Cells in Excel

The easiest way is to combine cells by using the CONCATENATE function in Excel. We can use this function in two ways. One is without space between two cells and the other is with space between two cells. We will learn both types below.


1.1 Perform the CONCATENATE Function to Combine Cells Without Space

In this sub-method, we want to combine Column B and Column C without space between them. First, we want to Combine the First and Last Names of Ben Johnson. To do that, we can use the CONCATENATE function. Let’s follow the steps below to learn!

Steps:

  • First of all, select cell D5, where we will combine the cell values of cells B5 and C5 by using the CONCATENATE function.

Perform the CONCATENATE Function to Combine Cells Without Space

  • Now, type the CONCATENATE function in the Formula Bar. the function is,

=CONCATENATE(B5, C5)

Perform the CONCATENATE Function to Combine Cells Without Space

  • After typing the CONCATENATE function in the Formula Bar, press Enter on your keyboard and we will be able to combine Ben and Johnson without space between the two words.

Perform the CONCATENATE Function to Combine Cells Without Space

  • Now, place your cursor Bottom-Right on cell D5, an AutoFill Sign pops up, and drag it downward.

Perform the CONCATENATE Function to Combine Cells Without Space

  • After dragging the AutoFill sign downward, we’ll get our desired output which has been given in the below screenshot.


1.2 Insert the CONCATENATE Function to Combine Cells with Space

After learning the CONCATENATE function without space, here we’ll learn how to combine cells by using the CONCATENATE function with space between the cells. Let’s follow the instructions below.

Steps:

  • First, select cell D5.

Insert the CONCATENATE Function to Combine Cells with Space

  • After selecting cell D5, type the CONCATENATE function in the Formula Bar. The function is,

=CONCATENATE(B5," ",C5)

Insert the CONCATENATE Function to Combine Cells with Space

  • Now, press Enter on your keyboard, and you will get Ben johnson as the output of the function.

Insert the CONCATENATE Function to Combine Cells with Space

  • After that, place your cursor Bottom-Right on cell D5, an AutoFill Sign pops up, and drag it downward.

  • After completing the above process, we will get the desired output.

Read More: How to Combine Cells with Same Value in Excel (3 Easy Ways)


2. Apply the Ampersand Symbol to Combine Cells in Excel

Ampersand Symbol is the most useful symbol to combine cells in Excel. This symbol is widely used in Excel to combine cells. In this method, we will learn some applications of the ampersand symbol to combine cells in Excel.


2.1 Insert the Ampersand Symbol to Combine Cells Without Space

In this sub-method, we’ll learn how to combine cells without space using the ampersand symbol in Excel. Here, we will combine cell B5 and cell C5, by using the ampersand symbol. Let’s follow the steps below.

Steps:

  • First of all, select cell D5, where we will combine cell B5 and cell C5.

Insert the Ampersand Symbol to Combine Cells Without Space

  • After selecting cell D5, type the formula in the Formula Bar. The formula is,

=A2&B2

  • Now, simply press Enter on your keyboard. And you will be able to combine cell B5 and cell C5 without giving space between them.

Insert the Ampersand Symbol to Combine Cells Without Space

  • Similarly, you’ll get your desired output in column D which has been given in the below screenshot.

Insert the Ampersand Symbol to Combine Cells Without Space


2.2 Perform the Ampersand Symbol to Combine Cells with Space

Now, we will learn how to combine cells with space between two cells by using the ampersand symbol in Excel. To combine cell B5 and cell C5. In cell D5, let’s follow the instructions below.

Steps:

  • First, from our dataset, we’ll select cell D5 to combine cell B5 and cell C5.

Perform the Ampersand Symbol to Combine Cells with Space

  • Now, type the formula in the Formula Bar. The formula is,

=B5&" "&C5

Perform the Ampersand Symbol to Combine Cells with Space

  • After typing the formula in the Formula Bar, simply press Enter on your keyboard and you will be able to get Ben Johnson as your desired output.

  • In the above ways, you will be able to combine column B and column C that has been given in the screenshot.

Perform the Ampersand Symbol to Combine Cells with Space


2.3 Use Ampersand Symbol and Dash to Combine Cells

In this sub-method, we’ll learn how to combine cells in Excel by using the ampersand symbol and dash symbol. Let’s follow these steps below to learn!

Steps:

  • First, select cell D5.

Use Ampersand Symbol and Dash to Combine Cells

  • After selecting cell D5, type the formula in the Formula Bar. The formula which is typing in the Formula Bar is,

=B5& "-" &C5

Use Ampersand Symbol and Dash to Combine Cells

  • Further then, press Enter on your keyboard and you will get Ben-Johnson in cell D5 as the return of the formula.

  • After completing the above process, you will get the return in column D of the function which is typing in cell D5.

Use Ampersand Symbol and Dash to Combine Cells


2.4 Apply the Ampersand Symbol and Text to Combine Cells

Here, we will learn a new method to combine cells by using the ampersand symbol along with the text. We have used a combination of cell reference and text to make sentences by using the ampersand symbol. Let’s follow the instructions below to learn!

Steps:

  • First, select cell D5 where we want to make a sentence by combining cell B5 and cell C5 with adding text and the ampersand symbol.

Apply the Ampersand Symbol and Text to Combine Cells

  • After that, type a formula in the Formula Bar. The formula which is typing in the Formula Bar is,

=B5&" has "&C5&" Offices"

Apply the Ampersand Symbol and Text to Combine Cells

  • Now, you will get USA has 48 Offices in the cell D5 as the output of the formula.

Apply the Ampersand Symbol and Text to Combine Cells

  • After that, place your cursor on the bottom-right of cell D5 and instantly an autoFill sign will appear in front of you. Drag the autoFill sign downward.

  • After dragging the autoFill sign, you will be able to get your desired output which has been given in the below screenshot.

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


3. Insert the CHAR Function to Combine Cells in Excel

In this method, we will learn the most interesting formula named the CHAR function to combine cells in Excel. To combine cells in Excel by using the CHAR function, let’s follow the steps below.

Steps:

  • First of all, select cell D5.

Insert the CHAR Function to Combine Cells in Excel

  • After selecting cell D5, type the CHAR function in the Formula Bar. The function in the Formula Bar is,

=B5&CHAR(10)&C5

Insert the CHAR Function to Combine Cells in Excel

  • Now, simply press Enter on your keyboard and you will get BenJohnson in cell D5 as the output of the function.

Insert the CHAR Function to Combine Cells in Excel

  • Similarly, you will be able to combine cells between Column B and Column C which has been given in the screenshot.

Insert the CHAR Function to Combine Cells in Excel

Read More: How to Combine Cells into One with Line Break in Excel (5 Methods)


4. Apply TEXT and TODAY Function to Combine Cells in Excel

Here, we will apply the Excel formula to combine cells named the TEXT and the TODAY functions. Let’s follow the instructions below to learn!

Steps:

  • First, select cell B5.

Apply TEXT and TODAY Function to Combine Cells in Excel

  • After that, type the TODAY function in the Formula Bar. The formula is in the Formula Bar,

=TODAY()

Apply TEXT and TODAY Function to Combine Cells in Excel

  • Now, press Enter on your keyboard and you will get 1/26/2022 as the return of that function.

Apply TEXT and TODAY Function to Combine Cells in Excel

  • After completing the above process, again select cell C5.
  • In cell C5, type a new formula. The formula is,

= “Today is “&TODAY()

  • Again, press Enter on your keyboard and you will get Today is 44587 which is a long number without a date formatting as the return of that function.

Apply TEXT and TODAY Function to Combine Cells in Excel

  • To give the number into a format select a new cell D5.

Apply TEXT and TODAY Function to Combine Cells in Excel

  • After selecting cell D5, again, type a new formula with double quotes in the Formula Bar. The formula is,

="Today is " &TEXT(TODAY(),"mm-dd-yy")

Apply TEXT and TODAY Function to Combine Cells in Excel

  • After completing the above process, again press Enter on your keyboard and you will get your desired output which has been given in the below screenshot.

Apply TEXT and TODAY Function to Combine Cells in Excel

Read More: How to Combine Two Cells in Excel with a Dash (5 Methods)


5. Apply the Number Format to Combine Cells in Excel

In this method, we’ll learn how to combine cells by using the Number format without using a formula. The dates or numbers are not affected, they can still be used in other calculations, as numeric values. Only the cell appearance is changed, not the cell value. Follow these steps to apply a custom number format that combines text and a formatted date.

Steps:

  • First, select cell C5.

Apply the Number Format to Combine Cells in Excel

  • Now, type the formula in the Formula Bar. The formula is,

=B5

Apply the Number Format to Combine Cells in Excel

  • After that, press Enter on your keyboard. You will get 26-Jan-19 as the output of the function.

  • Then, press Right-Click on your Mouse, and a window pops up. From that window click on Format Cells.

Apply the Number Format to Combine Cells in Excel

  • After clicking on the Format Cells, a new window named Format Cells instantly appears in front of you.
  • From that window, select Date Category from the Number Tab and also select a date type from the Type We select 14-Mar-12 for our work.

  • Now, select Custom Category from Number Tab. After that, you will see [$-en-US]d-mmm-yy;@ in the Type box.

Apply the Number Format to Combine Cells in Excel

  • In the Type box, type “We’ll go to USA at “[$-en-US]d-mmm-yy;@ and press OK.

Apply the Number Format to Combine Cells in Excel

  • After completing the above process, you will be able to change cell appearance, not the cell value.

  • Similarly, you can change the other cell’s appearance but not their values which have been given below screenshot.

Apply the Number Format to Combine Cells in Excel


6. Using the TEXTJOIN Function to Combine Cells in Excel

After learning the CONCATENATE function, Ampersand symbol method, CHAR formula, TEXT and TODAY formula, and Number format method, we’ll learn to combine cells in Excel by using the TEXTJOIN function. To combine cells in Excel by using the TEXTJOIN function, follow the instructions below.

Step 1:

  • First, select cell D5, where we will type the TEXTJOIN function.

Using the TEXTJOIN Function to Combine Cells in Excel

  • After selecting cell D5, type the TEXTJOIN function in the Formula Bar. The formula in the Formula Bar is,

=TEXTJOIN(“ “, TRUE, B5:C5)

Using the TEXTJOIN Function to Combine Cells in Excel

  • While completing to type the function in the Formula Bar, simply press Enter on your keyboard and you’ll get Ben Johnson as the return of the TEXTJOIN function.

Step 2:

  • Now, place your cursor on the bottom-right of cell D5, and instantly an autoFill sign will appear in front of you. Drag the autoFill sign downward.

Using the TEXTJOIN Function to Combine Cells in Excel

  • After completing the above process, you will be able to get your desired output.

Read More: How to Combine Names in Excel with Space (6 Approaches)


Things to Remember

👉 you can only use the TEXTJOIN function in Excel 2019 or later including Microsoft 365.


Conclusion

I hope all of the suitable methods mentioned above to combine cells will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo