Durjoy Paul
Concatenate can be defined as a process through which we can join two values together in a cell. But sometimes, you need to concatenate formula and text in Excel altogether. Then, this article is for you. We will show how to concatenate formulas in text in Excel with some effective examples. I hope you find this article very informative and interesting.
Download Practice Workbook
Download the practice workbook below.
4 Suitable Examples to Concatenate Formula and Text in Excel
To concatenate formula and text in Excel, we would like to show four effective examples through which you can have a clear view of this topic. Our first two examples will be based on single and multiple texts with formulas. In the final example, we would like to use the CONCATENATE function to combine text and formula.
1. Concatenate Single Text and Formula in Excel
Our first example is based on combining a single text with a formula. In this example, we would like to show you how to combine them effectively. Here, we don’t use any other function to combine them. To show the example, we take a dataset that contains some salesperson and their sales amount in several months. So, we would like to combine them. In order to understand the process, follow the steps.
📌 Steps
- To combine the text and formula, first, select cell G5.
- Then, write down the following formula.
=B5 & " : " & " $"& SUM(C5:F5)
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula
B5 & ” : ” & ” $”& SUM(C5:F5): In this formula, first, we define the cell B5 where we will get the text. After that, we need to use the ‘&’ sign before using any other character. Then, we use a colon sign. As we want to calculate the total sales amount of that particular person, we use the SUM function there. Finally, we will get our desired output Hopper : $16334.
Read More: How to Copy CONCATENATE Formula in Excel (5 Easy Ways)
2. Concatenate Multiple Texts and Formulas
Our second example is based on combining multiple texts and formulas. In this example, we would like to show you how to combine them effectively. Here, we don’t use any other function to combine them. To show the example, we take a dataset that contains the name of some salespeople and their sales amount over several months. So, we would like to combine multiple texts and find the total and average sales amount of that particular person. In order to understand the process, follow the steps.
📌 Steps
- To combine the text and formula, first, select cell H5.
- Then, write down the following formula using the SUM and AVERAGE functions.
="Total: $"&SUM(C5:F5)&" and Average: $"&AVERAGE(C5:F5) 
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula
“Total: $”&SUM(C5:F5)&” and Average: $”&AVERAGE(C5:F5): Here, we utilize the SUM function to add all the sales amount and use the AVERAGE function to get the mean of the sales amount. Along with these, we use multiple texts and use the ‘&’ sign to combine them. Finally, the output will be like Total: $16344 and Average: $4086.
Read More: How to Concatenate Number and Text in Excel (6 Methods)
Similar Readings
- How to Concatenate and Keep Number Format in Excel
- Combine Multiple Columns into One Column in Excel
- Opposite of Concatenate in Excel (4 Options)
- How to Concatenate Two Columns In Excel (5 Methods)
- Add a 1 in Front of Numbers in Excel [7 Special Tricks]
3. Combining Text and Formula with CONCATENATE Function
Our third example is based on using the CONCATENATE function to combine text and formula in Excel. In this example, we will utilize the CONCATENATE and SUM functions to do the job. Here, we would like to illustrate the process clearly for better understanding. So, follow the steps.
📌 Steps
- To combine the text and formula, first, select cell G5.
- Then, write down the following formula.
=CONCATENATE(B5," : ", "$",SUM(C5:F5))
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula
CONCATENATE(B5,” : “, “$”,SUM(C5:F5)): The CONCATENATE function helps to combine text and formula into a cell. Here, we define cell B5 where we will get the text. Then, we use a colon and ‘$’ sign before applying the formula. After that, we utilize the SUM function to add all the sales amounts for different months. Finally, we get our desired output Hopper : $16344.
Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)
4. Combining Text and Formula with TEXTJOIN Function
Our final example is based on using the TEXTJOIN function to combine text and formula in Excel. In this example, we will utilize the TEXTJOIN and SUM functions to do the job. Here, we would like to illustrate the process clearly for better understanding. So, follow the steps.
📌 Steps
- To combine the text and formula, first, select cell G5.
- Then, write down the following formula.
=TEXTJOIN("",TRUE,"The Total Sales Amount of ",B5," is $",SUM(C5:F5))
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
🔎 Breakdown of the Formula
TEXTJOIN(“”,TRUE,”The Total Sales Amount of “,B5,” is $”,SUM(C5:F5)): The TEXTJOIN function can concatenate a list or range of text strings using a delimiter. So, We need to define the delimiter first. Then, define whether you want to ignore empty cells or not. We want to ignore empty cells. Thus, define TRUE. After that, we can use the preferred text. Finally, the output will be like “The Total Sales Amount of Hopper is $16344”.
Read More: How to Combine Text from Two or More Cells into One Cell in Excel (5 Methods)
Conclusion
We have shown four effective examples using the basic Excel formula, the TEXTJOIN, and CONCATENATE functions to concatenate formula and text in Excel. All of these examples are fairly easy to use. I hope we covered all possible areas regarding the topic. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.
Related Articles
- How to Concatenate Cells with If Condition in Excel (5 Examples)
- Excel CONCATENATE Showing Formula Not Result (5 Solutions)
- Combine CONCATENATE & TRANSPOSE Functions in Excel
- How to Concatenate Arrays in Excel (With 2 Conditions)
- 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
