# How to Concatenate Number and Text in Excel (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Concatenate in Excel means linking two or more cells together into a chain or series of data. While dealing with a large dataset, you may need to concatenate number and text together in Excel. It may seem difficult after hearing the word concatenate as we do not use it generally. But believe us, concatenating number or text in Excel is a very easy task to do. In this article, we are going to discuss the methods to concatenate number and text in Excel. So, let’s get started.

## 6 Methods to Concatenate Number and Text in Excel

You can add your text and numbers by using the various features and formulas of Excel. We have tried to cover all the methods in this article. We have taken a dataset of Students‘ names with their Student IDs. Now, we want to add these texts and numbers. Not to mention, we have used the Microsoft 365 version. You can use any other version at your convenience.

### 1. Using Ampersand Operator

You may concatenate the text and numbers by using the Ampersand operator. It joins two or more cells with an “&” sign. To do it, just follow the steps below.

📌 Steps:

• Firstly, go to cell D5 and enter the formula.
=B5&”(” &C5 &”)”

Here, the B5&”(” &C5 &”)” syntax concatenates the text of B5 and the number of C5 cells with the Ampersand (&) operator. • Then, press ENTER and drag down the Fill Handle tool for other cells. Eventually, the output result looks like the image below. ### 2. Employing CONCAT Function

We can also use the CONCAT function to add text and numbers. Generally, this function links two or more cells into a series, but it cannot ignore the empty cells. It also doesn’t have compatibility with the older version. But in the newer version, it possesses a strong position as it is very easy to use. So, we have discussed it with the necessary steps below.

📌 Steps:

• In the beginning, move to cell D5 and insert the formula.
=CONCAT(B5,”(“,C5,”)”)

It concatenates the text of B5 and the number that existed in cell C5. Finally, you will get the result after pressing ENTER and dragging it down to other cells. ### 3. Applying CONCATENATE Function

Like the CONCAT function, the CONCATENATE function also combines multiple cell ranges. It is compatible with older versions. Follow the steps to use this function.

📌 Steps:

• Firstly, select cell D5 and write up the following formula.
=CONCATENATE(B5,”(“,C5,”)”)

This CONCATENATE(B5,”(“,C5,”)”) syntax combines the values of B5 and C5. Consequently, the result will look like the image below after pressing ENTER. ### 4. Utilizing TEXTJOIN Function

The TEXTJOIN function, available from Microsoft Excel 2019, concatenates a list or range of text strings into a single string using a delimiter. It can include both empty and non-empty cells. We will demonstrate to you the steps for better visualization.

📌 Steps:

• Initially, go to cell D5 and input the formula.
=TEXTJOIN(“-“,TRUE,B5,C5)

The TEXTJOIN(“-“,TRUE,B5,C5) syntax takes “” as a delimiter. The TRUE command ignores the empty cell and then links up the B5 and C5 cells to show the result. • Then, press ENTER, and the result will look like the image shown below. ### 5. Combining CONCAT and TRANSPOSE Functions

We mentioned earlier the working procedure of the CONCAT function. The TRANSPOSE function switches the cells. You can concatenate multiple cells by using these two functions combinedly. Follow the steps to do it.

📌 Steps:

• Firstly, go to cell D5 and insert the formula.
=CONCAT(B5,TRANSPOSE(C5:D5))

The TRANSPOSE(C5:D5) syntax switches the cells of C5 and D5 and the CONCAT function combines the result of the TRANSPOSE function with cell B5. • Then, press ENTER and drag it down to get the result. ### 6. Using TEXT and TODAY Functions

Furthermore, you can use the TEXT and TODAY functions to concatenate the text and numbers. The TODAY function takes the current date and the TEXT function combines the text and numbers. Follow the steps below to get a decent idea.

📌 Steps:

• Primarily, move to cell D5 and write the formula,
=TEXT(TODAY(),”mmmm dd, yyyy”)&” Expense is \$”&C5

In the above formula, the TODAY function finds the current date, The Ampersand then adds this text of C5. Finally, the TEXT function returns the entire output in text format.

• Then press ENTER and drag down for other cells. Finally, you’ll get your desired result. Read More: How to Concatenate Date/Day, Month, and Year in Excel

## How to Concatenate Text and Formula in Excel

You can also combine the text and formula in Excel. To do this operation, we use the TEXTJOIN and the SUM function together. The SUM function calculates the arithmetic sum of multiple cells, and the TEXTJOIN concatenates the cells with the delimiter. Please follow the steps to use the text and formula together.

📌 Steps:

• First of all, select cell F5 and enter the formula.
=TEXTJOIN(“”,TRUE,”The Total Expenses of “,B5,” is \$”,SUM(C5,D5,E5))

In the above formula, the SUM(C5, D5, E5) formula calculates the sum of C5:E5 and the TEXTJOIN function combines the text of B5 and this summation. It takes “,” as a delimiter. Finally, the result looks like the image below. ## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself. ## Related Articles #### Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  