Md. Abdur Rahim Rasel
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.
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.
- Now, type the CONCATENATE function in the Formula Bar. the function is,
=CONCATENATE(B5, C5)
- 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.
- Now, place your cursor Bottom-Right on cell D5, an AutoFill Sign pops up, and drag it downward.
- 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.
- After selecting cell D5, type the CONCATENATE function in the Formula Bar. The function is,
=CONCATENATE(B5," ",C5)
- Now, press Enter on your keyboard, and you will get Ben johnson as the output of the function.
- 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.
- 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.
- Similarly, you’ll get your desired output in column D which has been given in the below screenshot.
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.
- Now, type the formula in the Formula Bar. The formula is,
=B5&" "&C5
- 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.
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.
- After selecting cell D5, type the formula in the Formula Bar. The formula which is typing in the Formula Bar is,
=B5& "-" &C5
- 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.
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.
- After that, type a formula in the Formula Bar. The formula which is typing in the Formula Bar is,
=B5&" has "&C5&" Offices"
- Now, you will get USA has 48 Offices in the cell D5 as the output of the formula.
- 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.
- After selecting cell D5, type the CHAR function in the Formula Bar. The function in the Formula Bar is,
=B5&CHAR(10)&C5
- Now, simply press Enter on your keyboard and you will get BenJohnson in cell D5 as the output of the function.
- Similarly, you will be able to combine cells between Column B and Column C which has been given in the screenshot.
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.
- After that, type the TODAY function in the Formula Bar. The formula is in the Formula Bar,
=TODAY()
- Now, press Enter on your keyboard and you will get 1/26/2022 as the return of that function.
- 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.
- To give the number into a format select a new cell D5.
- 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")
- 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.
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.
- Now, type the formula in the Formula Bar. The formula is,
=B5
- 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.
- 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.
- In the Type box, type “We’ll go to USA at “[$-en-US]d-mmm-yy;@ and press OK.
- 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.
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.
- After selecting cell D5, type the TEXTJOIN function in the Formula Bar. The formula in the Formula Bar is,
=TEXTJOIN(“ “, TRUE, B5:C5)
- 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.
- 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.
