Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Concatenate Formula and Text in Excel (4 Examples)

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)

Concatenate Single Text and Formula in Excel

  • After that, press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Combine Single Text and Formula in Excel

πŸ”Ž 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)

Concatenate Multiple Texts and Formulas

  • After that, press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Combine Multiple Texts and Formulas

πŸ”Ž 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


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))

Combining Text and Formula with CONCATENATE Function in Excel

  • 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))

Combining Text and Formula with TEXTJOIN Function

  • After that, press Enter to apply the formula.

  • Then, drag the Fill Handle icon down the column.

Concatenate Text and Formula with TEXTJOIN Function

πŸ”Ž 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

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo