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.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

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.

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.

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.

• Sequentially, press ENTER.

Eventually, you will get the result.

Read More: Add Text and Formula in the Same Cell in Excel (4 Examples)

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.

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.

• Then, press ENTER and drag down the Fill Handle tool.

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

Similar Readings

• How to Find And Replace Values Using Wildcards in Excel
• Find And Replace Multiple Values in Excel (6 Quick Methods)
• How to Replace Special Characters in Excel (6 Ways)
• Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
• How to Replace Text in Excel Formula (7 Easy Ways)

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.

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.

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

Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)

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.

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.

Read More: How to Add Text to Cell in Excel (6 Easy Methods)

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.

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.

Read More: How to Use the Substitute Function in Excel VBA (3 Examples)

Practice Section

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

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

• How to Find and Replace Asterisk (*) Character in Excel
• Find and Replace Carriage Return in Excel (with Quick Steps)
• How to Find and Replace in Excel Column (6 Ways)
• Excel VBA: How to Find and Replace Text in Word Document
• How to Find and Replace within Selection in Excel (7 Methods)
• Excel VBA to Find and Replace Text in a Column (2 Examples)