Fahim Shahriyar Dipto
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.
Download Practice Workbook
Download the following practice workbook. It will help you realize the topic more clearly.
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.
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.
Read More: How to Concatenate Two Columns In Excel (5 Methods)
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.
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.
Read More: How to Concatenate Columns in Excel (8 Simple Methods)
Similar Readings
- Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)
- Carriage Return in Excel Formula to Concatenate (6 Examples)
- How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)
- Concatenate Date That Doesn’t Become Number in Excel (5 Ways)
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
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.
This CONCATENATE(B5,”(“,C5,”)”) syntax combines the values of B5 and C5.
Consequently, the result will look like the image below after pressing ENTER.
Read More: How to Concatenate String and Integer using VBA
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.
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.
Read More: Combine Multiple Columns into One Column in Excel
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.
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.
Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)
Similar Readings
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Not Working in Excel (3 Reasons with Solutions)
- How to Combine Rows into One Cell in Excel
- Merge Rows in Excel (2 Easy Methods)
- How to Concatenate with Space in Excel (3 Suitable Ways)
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,
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.
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.
Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to concatenate numbers and text in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website Exceldemy, a one-stop Excel solution provider to find out diverse kinds of excel methods. Thanks for your patience in reading this article.
Related Articles
- CONCATENATE vs CONCAT in Excel (2 Ideal Examples)
- How to Concatenate Names in Excel (5 Easy Ways)
- Concatenate Cells but Keep Text Formatting in Excel
- How to Concatenate and Keep Number Format in Excel
- Concatenate Rows in Excel with Comma (4 Easy Methods)
- How to Concatenate Rows in Excel (11 Methods)
- Concatenate Multiple Cells with Comma in Excel (4 Ways)
