Combine Text and Formula in Excel (4 Simple Ways)

Whenever working with Excel, sometimes you will need to combine both text and formula into the same cell. The main objective of this article is to explain how you can combine text and formula in Excel.


Download Practice Workbook


4 Simple Ways to Combine Text and Formula in Excel

Here, I have taken the following dataset to show you how you can combine text and formula in Excel. I will explain 4 simple ways of doing it.

4 Simple Ways to Combine Text and Formula in Excel


1. Using Ampersand (&) Operator to Combine Text and Formula in Excel

In this first method, I will explain how to combine text and formula in Excel using the Ampersand (&) operator.

Let’s see the Steps.

Steps:

  • Firstly, select the cell where you want to combine text and formula. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=B5&"'s Total Marks: "&SUM(C5:D5)

Formula Breakdown

  • SUM(C5:D5) —-> Here, the SUM function will calculate the Summation of cells C5 to D5.
    • Output: 150
  • B5&”‘s Total Marks: ” —-> Now, the Ampersand (&) operator will combine the given texts.
    • Output: “Rachel’s Total Marks: “
  • B5&”‘s Total Marks: “&SUM(C5:D5) —-> turns into
    • “Rachel’s Total Marks: “&150 —-> Again the Ampersand (&) operator will combine the text and formula. 
      • Output: “Rachel’s Total Marks: 150”
      • Explanation: Here, the Ampersand (&) finally combines text and the SUM function.
  • Finally, press ENTER to get the result.

Using Ampersand (&) Operator to Combine Text and Formula in Excel

Using Ampersand (&) Operator to Combine Text and Formula in Excel

Here, you can see I have copied my formula to all the other cells.

Finally, in the following image, you can see that I have combined text and formula using the Ampersand operator.


2. Use of TEXT Function to Combine Text and Formula in Excel

In this method, I will show you how to combine text and formula in Excel using the TEXT function. I will explain 2 different examples.


Example-01: Using TEXT Function

In this example, I will use the TEXT function to combine text and formula. Here, I have taken the following dataset to explain this example. I will combine text and formula to show the Project Span.

Use of TEXT Function to Combine Text and Formula in Excel

Steps:

  • Firstly, select the cell where you want to combine text and formula. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
="From "&TEXT(C5,"dd-mmm-yyyy")&" to "&TEXT(D5,"dd-mmm-yyyy")

Formula Breakdown

  • TEXT(D5,”dd-mmm-yyyy”) —-> turns into
    • TEXT(44630,”dd-mmm-yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
      • Output: “10-Mar-2022”
  • TEXT(C5,”dd-mmm-yyyy”) —-> turns into
    • TEXT(44624,”dd-mmm-yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
      • Output: “04-Mar-2022”
  • “From “&TEXT(C5,”dd-mmm-yyyy”)&” to “&TEXT(D5,”dd-mmm-yyyy”) —-> turns into
    • “From “&”04-Mar-2022″&” to “&”10-Mar-2022” —-> Here, the Ampersand (&) operator combines these texts.
      • Output: “From 04-Mar-2022 to 10-Mar-2022”
      • Explanation: Here, the Ampersand (&) finally combines text and the TEXT function.
  • Finally, press ENTER to get the result.

  • Now, drag the Fill Handle to copy the formula.

Use of TEXT Function to Combine Text and Formula in Excel

Here, you can see I have copied my formula to all the other cells.

Finally, in the following picture, you can see that I have combined text and formula.

Use of TEXT Function to Combine Text and Formula in Excel

Read More: How to Combine Text and Number in Excel (4 Suitable Ways)


Example-02: Using TEXT & TODAY Functions

In this example, I will use the TEXT function along with the TODAY function and combine text and formula. Here, I have taken the following dataset to explain this example. I will combine text and formula to show the Order Date and Delivery Date.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to combine text and formula.
  • Secondly, in that cell write the following formula.
="Order Date: "&TEXT(TODAY(),"mm/dd/yyyy")

Use of TEXT Function to Combine Text and Formula in Excel

Formula Breakdown

  • TODAY() —-> Here, the TODAY function will return the current date.
    • Output: 44775
  • TEXT(TODAY(),”mm/dd/yyyy”) —-> turns into
    • TEXT(44775,”mm/dd/yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
      • Output: “08/02/2022”
  • “Order Date: “&TEXT(TODAY(),”mm/dd/yyyy”) —-> turns into
    • “Order Date: “&”08/02/2022” —-> Here, the Ampersand (&) operator combines these texts.
      • Output: “Order Date: 08/02/2022”
      • Explanation: Here, the Ampersand (&) finally combines text and the TEXT function.
  • Finally, press ENTER to get the result.

Now, I will combine text and formula to show the Delivery Date.

  • Firstly, select the cell where you want to combine text and formula.
  • Secondly, in that cell write the following formula.
="Delivery Date: "&TEXT(TODAY()+3,"mm/dd/yyyy")

Use of TEXT Function to Combine Text and Formula in Excel

Formula Breakdown

  • TODAY()+3 —-> Here, the TODAY function will return the current date and then Sum 3 with the current date.
    • Output: 44778
  • TEXT(TODAY()+3,”mm/dd/yyyy”) —-> turns into
    • TEXT(44778,”mm/dd/yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
      • Output: “08/05/2022”
  • “Delivery Date: “&TEXT(TODAY()+3,”mm/dd/yyyy”) —-> turns into
    • “Delivery Date: “&”08/05/2022” —-> Here, the Ampersand (&) operator combines these texts.
      • Output: “Delivery Date: 08/05/2022”
      • Explanation: Here, the Ampersand (&) finally combines text and the TEXT function.
  • Finally, press ENTER to get the result.

Now, in the following picture, you can see that I have combined text and formula.

Use of TEXT Function to Combine Text and Formula in Excel

Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)


Similar Readings


3. Employing Format Cells Feature to Combine Text and Formula in Excel

In this method, I will explain how you can combine text and formula in Excel using the Format Cells feature. Here, I have taken the following dataset to explain this example. I will show Total Sales and Total Profit by combining text and formula.

Employing Format Cells Feature to Combine Text and Formula in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate Total Sales. Here, I selected cell C9.
  • Secondly, in cell C9 write the following formula.
=SUM(C5:C8)

Here, the SUM function will return the Summation of cells C5 to C8.

  • Finally, press ENTER to get the result.

Employing Format Cells Feature to Combine Text and Formula in Excel

Now, I will calculate the Total Profit.

  • Firstly, select the cell where you want to calculate the Total Profit. Here, I selected cell D9.
  • Secondly, in cell D9 write the following formula.
=SUM(D5:D8)

Here, the SUM function will return the Summation of cells C5 to C8.

  • Finally, press ENTER to get the result.

Employing Format Cells Feature to Combine Text and Formula in Excel

  • After that, Right-click on the cell where you want to combine text and formula.
  • Next, select Format Cells.

Now, a dialog box named Format Cells will appear.

  • Firstly, select Custom.
  • Secondly, select the Number format you want.

  • Thirdly, modify the format as you want.
  • Finally, select OK.

Employing Format Cells Feature to Combine Text and Formula in Excel

Now, you can see the cell is formatted the way I selected, and it combines text and formula.

Here, by following the previous step, open the Format Cells dialog box for Total Profit.

  • Firstly, select Custom.
  • Secondly, select the Number format you want.

  • Thirdly, modify the format as you want.
  • Finally, select OK.

Employing Format Cells Feature to Combine Text and Formula in Excel

Now, you can see that, I have combined text and formula together.

In this method, the numbers are still stored as Number. To show that I will calculate Profit Percentage from these values.

  • Firstly, select the cell where you want to calculate Profit Percentage. Here, I selected cell D11.
  • Secondly, in cell D11 write the following formula.
=D9/C9*100%

Here, the Total Profit is divided by Total Sales and the result is multiplied by 100%. This formula will return the Profit Percentage.

  • Finally, press ENTER and you will get your result.

Now, you can see the formula is working. That means the numbers are still stored as Number.

Employing Format Cells Feature to Combine Text and Formula in Excel

Read More: How to Combine Text and Numbers in Excel and Keep Formatting


4. Using CONCATENATE Function to Combine Text and Formula in Excel

In this method, I will show you how to combine text and formula using the CONCATENATE function.

Let’s see the steps

Steps:

  • Firstly, select the cell where you want to combine text and formula. Here, I selected cell E5.
  • Secondly, in cell E5 write the following formula.
=CONCATENATE(B5,"'s Total Marks: ",SUM(C5:D5))

Using CONCATENATE Function to Combine Text and Formula in Excel

Formula Breakdown

  • SUM(C5:D5) —-> Here, the SUM function will calculate the Summation of cells C5 to D5.
    • Output: 150
  • CONCATENATE(B5,”‘s Total Marks: “,SUM(C5:D5)) —-> turns into
    • CONCATENATE(“Rachel”,”‘s Total Marks: “,150) —-> Here, the CONCATENATE function will combine these texts.
      • Output: “Rachel’s Total Marks: 150”
      • Explanation: Here, I combined texts and formula using the CONCATENATE function.
  • Finally, press ENTER to get the result.

  • Now, drag the Fill Handle the copy the formula.

Using CONCATENATE Function to Combine Text and Formula in Excel

Here, you can see I have copied my formula to all the other cells.

Using CONCATENATE Function to Combine Text and Formula in Excel

Finally, in the following image, you can see that I have combined text and formula using the CONCATENATE function.

Using CONCATENATE Function to Combine Text and Formula in Excel


💬 Things to Remember

  • It should be noted that whenever combining text and formula, the text should be written in between double inverted commas.

Practice Section

Here, I have provided a practice sheet for you to practice how to combine text and formula in Excel.


Conclusion

To conclude, I tried to cover how to combine text and formula in Excel. I explained 4 different methods with different examples. I hope this article was helpful for you. Lastly, if you have any questions let me know in the comment section below.


Related Articles

Mashhura
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo