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

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.

## 5 Ways to Add Text Before a Formula in Excel

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. 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. • Then, press ENTER and drag the Fill Handle tool down for the same formula to other cells. 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. 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. Eventually, you will get the result. ### 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. • 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 also. 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. Eventually, you will get a result just like the image below. ### 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. • 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) • Press ENTER to get the summation. • Then, go to the Developer tab >> choose Visual Basic. • A window will appear. Select the Insert tab >> pick Module >> move to Module1. Then write up the code in the box. ``````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`````` Subsequently, the result will be displayed in your desired column. ## 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. • 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. 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. • Then, drag it down for other cells after pressing ENTER. Finally, you will get the results just like the above image.

## 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. • Eventually, press ENTER and drag it down to get the final result like the image below. ## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself. ## Related Articles Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts  5 Excel Tips
You Never Knew  