Mashhura Jahan
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.
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.
- “Rachel’s Total Marks: “&150 —-> Again the Ampersand (&) operator will combine the text and formula.
- Finally, press ENTER to get the result.
- Now, drag the Fill Handle the copy the formula.
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.
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(44630,”dd-mmm-yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
- 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”
- TEXT(44624,”dd-mmm-yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
- “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.
- “From “&”04-Mar-2022″&” to “&”10-Mar-2022” —-> Here, the Ampersand (&) operator combines these texts.
- Finally, press ENTER to get the result.
- Now, drag the Fill Handle to copy the formula.
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.
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")
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”
- TEXT(44775,”mm/dd/yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
- “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.
- “Order Date: “&”08/02/2022” —-> Here, the Ampersand (&) operator combines these texts.
- 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")
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”
- TEXT(44778,”mm/dd/yyyy”) —-> Here, the TEXT function will format the number to the given Date format.
- “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.
- “Delivery Date: “&”08/05/2022” —-> Here, the Ampersand (&) operator combines these texts.
- Finally, press ENTER to get the result.
Now, in the following picture, you can see that I have combined text and formula.
Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)
Similar Readings
- How to Add Text in Excel Spreadsheet (6 Easy Ways)
- Add a Word in All Rows in Excel (4 Smart Methods)
- How to Add Text to Beginning of Cell in Excel (7 Quick Tricks)
- Add Text to End of Cell in Excel (6 Easy Methods)
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.
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.
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.
- 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.
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.
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.
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))
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.
- CONCATENATE(“Rachel”,”‘s Total Marks: “,150) —-> Here, the CONCATENATE function will combine these texts.
- Finally, press ENTER to get the result.
- Now, drag the Fill Handle the copy the formula.
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 CONCATENATE function.
💬 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.
