How to Add Text Before a Formula in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to add text in front of an Excel formula for better understanding and visualization. It makes the output more meaningful. What’s going on in your mind after hearing this? Is it an arduous task? Nope! It is quite simple and easy to add text before a formula in Excel. In this article, we will show how to add text before any formula in Excel. So, let’s get started.


Watch Video – Add Text Before a Formula in Excel



How to Add Text Before a Formula in Excel: 5 Ways

You can add text before a formula in several ways. We have tried to cover all of them in our article. We have taken a dataset of Total Expenses for three months i.e. January, February, and March along with First Name and Last Name columns. Now, we want to sum the expenses and the text before the formula in calculating the total.

Dataset

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.


1. Using Ampersand (&) Operator

You can add the text before as a formula by using the Ampersand (&) operator only. You can also use the operator to add text after the formula.


1.1 Add Text Before Formulas

Here we will add text before the formula for the above dataset using the Ampersand (&) operator. Follow the steps to do it.

Steps:

  • Firstly, move to cell G5 and write down the formula.
="The Total Expenses of "&B5&C5&" is "&"$"&SUM(D5,E5,F5)

This will add the value of cells B5 and C5 and the text of “The Total Expenses” before the SUM function by the Ampersand (&) operator.

Add Text in a Formula Using Ampersand (&) Operator in Excel

  • Then, press ENTER and drag the Fill Handle tool down for the same formula to other cells.

Fill handle

Eventually, you will get the result like the image below.


1.2 Add Text Between Two Formulas

In this section, we will consider two formulas, and the text will be placed between these two formulas. We will assume the same example above, but in addition, we will use the TEXT and TODAY functions.

Steps:

  • Firstly, go to cell G5 and insert the below formula.
=TEXT(TODAY(), "mmmm dd, yyyy")&" Expenses is $"&SUM(D5, E5, F5)

In the above formula, the TODAY function finds the current date, and the SUM function will add the text from D5 to F5. The Ampersand then adds this text. Finally, the TEXT function returns the entire output in text format.

Add Text between Two Formulas in Excel

Finally, you will get the desired result after pressing ENTER and using the Fill Handle tool.


1.3. Add Line Break Between Two Formulas

We can also use line breaks between two more formulas. Let’s think about the same example using the previous method to describe this process.

Steps:

  • Initially, enter the formula in cell G5.
=TEXT(TODAY(), "mmmm dd, yyyy")&CHAR(10)&" Expenses is $"&SUM(D5,E5,F5)
  • Sequentially, press ENTER.

Add Line Break Between Two Formulas

Eventually, you will get the result.

Read More: Add Text and Formula in the Same Cell in Excel


2. Using CONCAT Function

Let’s do the same thing that we did in method 1, but this time we will use the CONCAT function. This function aggregates text from multiple cells, excluding any delimiter.

Steps:

  • Firstly, move to cell G5 and enter the formula.
=CONCAT("The Total Expenses of ",B5, C5," is $",SUM(D5,E5,F5))

This function will add the argument entered in the parenthesis and display the added result.

Add Text before a Formula Using CONCAT Function in Excel

  • Then, press ENTER and drag it down to get the result like below.


3. Utilizing CONCATENATE Function

Like the CONCAT function, you can add the text string with the CONCATENATE function. It will give the same result as well. But the CONCAT function doesn’t give you the delimiter or ignore the empty cells. The CONCATENATE will work with the earlier versions also. Please see the below steps.

Steps:

  • Primarily, select cell G5 and enter the following formula.
=CONCATENATE("The Total Expenses of ", B5, C5," is $", SUM(D5, E5, F5))
  • Then, press ENTER and drag down the Fill Handle tool.

Add Text before a Formula Using CONCATENATE Function in Excel

Eventually, you will get a result just like the image below.

Read More: How to Add Text to Multiple Cells in Excel

4. Applying TEXTJOIN function

In this method, we will show the use of the TEXTJOIN function in our task, and again let’s consider the same example overhead. Although we use the TEXTJOIN function to add text, it is an extremely versatile function and can be used in many ways other than just adding text

Steps:

  • The following formula should be entered at the beginning.
=TEXTJOIN("",TRUE,"The Total Expenses of ",B5,C5," is $",SUM(D5,E5,F5))

This function will add the text before the function.

Add Text before a Formula Using the TEXTJOIN function in Excel

  • Press ENTER and drag down the same formula to get the result like the below screenshot.


5. Employing VBA Code

You can use VBA macros to add the text before a formula. This is quite a long time compared to the other methods. But applying a code will express your diverse qualities over the Excel tools. We have demonstrated the steps to do it.

Steps:

  • In the very beginning, you have to calculate the total sum of your dataset. To do that, go to cell G5 and enter the formula.
=SUM(D5:F5)

Add Text before a Formula Using VBA in Excel

  • Press ENTER to get the summation.

  • Then, go to the Developer tab >> choose Visual Basic.

Add Text before a Formula Using VBA in Excel

  • A window will appear. Select the Insert tab >> pick Module >> move to Module1. Then write up the code in the box.

Module Window

Sub Adding_Text_Before_Formula()
Dim x As Range
For Each x In Selection
If x.Value <> "" Then x.Value = "The Total Expenses are $" & x.Value
Next
End Sub

VBA code to add text before a formula in Excel

Subsequently, the result will be displayed in your desired column.

Read More: How to Add Text in Excel Spreadsheet


How to Add Text Using Flash Fill Feature

For adding or combining two or more text in a cell, you can use the Flash Fill feature of MS Excel. Let’s have a dataset of some person’s First name and Last name. Now we will add the two portions of the names and show them in a single column as Full Names.

Steps:

  • Firstly, enter the Full Name in cell C4.

How to Add Text using Flash Fill Feature in Excel

  • Secondly, go to the Home tab >> select Fill >> choose Flash Fill.

Finally, you will get the parts of the names as Full Names just like in the image below.


How to Add Text in the Middle of a Cell in an Excel Formula

You can use the LEFT and MID functions to add text in the middle of a cell. You can also add multiple texts with the formula. Suppose you have a dataset where you inserted your Employee’s Job ID (see the image below). You want to change the IDs by entering new text in the middle.

How to Add Text in the Middle of a Cell in an Excel Formula

Steps:

  • Firstly, go to cell D5 and insert the formula.
=LEFT(C5,3)&"M"&MID(C5,4,5)

The formula here divides the text in C5 into two parts. The LEFT function returns the first three characters of the ID of C5, and the MID function returns 5 characters from the 3rd one of the ID, as our IDs have 7 characters each. The Ampersand Operator adds the letter M between these two parts.

Using LEFT and MID functions to add text before a formula in Excel

  • Then, drag it down for other cells after pressing ENTER.

Finally, you will get the results just like the above image.

Read More: How to Add Text to Cell Value in Excel


How to Add Text to the Beginning of the Cell in Excel

The REPLACE function in Excel substitutes characters in a text string according to their position. We will use this unique property of this function to add a piece of text to the beginning of the original data cell in Excel. In our dataset, we have taken the Student ID where we want to put a text at the beginning of the New ID.

Steps:

  • First of all, go to cell D5 and enter the formula.
=REPLACE(C5,1,0, "S")

The REPLACE(C5,1,0, “S”) syntax will take the value of C5, start_num as 1, and the num_chars as 0 to enter the text at the beginning and “S” as the starting text.

Adding Text to the Beginning of the Cell in Excel formula

  • Eventually, press ENTER and drag it down to get the final result like the image below.

Read More: How to Add Text to Beginning of Cell in Excel


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook


Conclusion

That’s all about today’s session. And these are some easy methods on how to convert time zones 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


<< Go Back to Excel Add Text to Cell Value | Concatenate Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo