Md. Abdullah Al Murad
For better understanding and visualization sometimes, we need to add text in front of an Excel formula. In this article, I will show how to add text before any formula in Excel.
Downloadable Practice Workbook
4 Ways to Add Text before a Formula in Excel
1. Add Text in a Formula Using Ampersand (&)
In Excel, we can easily add text using ampersand before, after, and between a formula. For showing the process let’s assume we have a dataset of some person’s expenses of specific 3 months. Now we want to show the total expenses with their names in a specific column.
Formula Explanation
I have used text at the beginning of the formula using Ampersand (&). Also using this syntax, I have combined the columns here which are First Name and Last Name. For the formula, SUM is used to calculate the total expenses for each person.
SUM(number1, [number2]......)SUM is another Excel function that takes numbers in its parameter and returns the total summation of the passed number.
1.1 Add Text before Formulas
Here we will add text before the formula for the above example using the ampersand.
Step 1: Enter the formula in cell F4.
="The Total Expenses of "&A4&B4&" is "&"$"&SUM(C4,D4,E4)
Step 2: Copy down the formula up to F10.
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, I will use one more function which is TODAY() at the beginning.
Steps: Enter the formula in cell F4.
=TEXT(TODAY(), "mmmm dd, yyyy")&" Expenses is $"&SUM(C4,D4,E4)
Formula Explanation
Here I have used TEXT and TODAY functions additionally.
=TEXT(Value you want to format, "Format code you want to apply")So, the first section of this function takes the values and I have used the TODAY function here as we want today’s date value. And the other section takes the formatting of the value.
By using this function, we will get today’s date. For getting the updated date we do not need to pass any value in the parameter of this function. But we can modify today’s date by passing the values.
Step 2: Copy down the formula up to F10.
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.
Formula Explanation
Here using the CHAR function, we have created a line break. This function with 10 values in parameter CHAR(10) returns a line break and can be used to add a line break to text in a formula.
Step 1: Enter the formula in cell F4.
=TEXT(TODAY(), "mmmm dd, yyyy")&CHAR(10)&" Expenses is $"&SUM(C4,D4,E4)
Step 2: Then select the cell and then choose the Wrap Text option above.
Step 3: Copy down the formula up to F10.
Read More: Add Text and Formula in the Same Cell in Excel (4 Examples)
2. Add Text before a Formula Using CONCAT Function
CONCAT is another function that provides the ability to add Text. Let’s do the same thing that we have done in method 1 but here we will use the CONCAT function.
Formula Explanation
Here we have used the CONCAT function to add text. The syntax of this function is:
CONCAT (text1, text2, [text3], ...)In the parameter, we can pass any text value, number, and even formulas. As we need to add text before that’s why first I have passed text and then the formula. You can also use the CONCATENATE keyword to denote the function as both works in the same way.
For an example:
=CONCAT(“Sum is”,SUM(A2:A10))Step 1: Enter the below formula in cell F4. The formula syntax will be:
So our actual formula will be:
=CONCAT("The Total Expenses of ",A4,B4,"is $",SUM(C4,D4,E4))(Note: Concat is a shorter usable form of Concatenate)
Step 2: Copy down the formula up to F10.
Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
Similar Readings:
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- How to Find and Replace Using Formula in Excel (4 Examples)
- Find and Replace in Excel Column (6 Ways)
- How to Find and Replace Asterisk (*) Character in Excel
- How to Use the Substitute Function in Excel VBA (3 Examples)
3. Add Text before a Formula Using TEXTJOIN function
In this method, we will show the uses of the TEXTJOIN function in our task, and again let’s consider the same example overhead.
Note: TEXTJOIN is a very versatile function and can be used in many ways other than just adding text. For more info check out this link: https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
Formula Explanation
This is another built-in Excel function. The syntax of the function is like this:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)The first section of this function takes the delimiter
Delimiter- It is known as the separator of our text. As I didn’t use anything so it will not separate the text but we can modify it as per requirements.
ignore empty- It takes if we want to ignore empty cells or not bypassing True or False.
After these two sections, we can pass any text or formula according to our needs. Â
For an example:
TEXTJOIN(“,”, True, “First Name”, “Last Name”)Step 1: Enter the formula in cell F4.
=TEXTJOIN("",TRUE,"The Total Expenses of ",A4,B4," is $",SUM(C4,D4,E4))Â 
Step 2: Copy down the formula up to F10.
Read More: How to Replace Text in Excel Formula (7 Easy Ways)
4. Add Text before a Formula Using VBA
In this stage, we are going to add text before the formula using the VBA code. Let’s imagine the same scenario above but this time we have the Total Expenses column filled with the summation of total expenses for each person. But we will attach text before this formula using VBA code.
Step 1: Select the range in which you will add text before the formula. Here I have selected cells F4 to F10.
Step 2: Hold it and use Keyboard Shortcut Alt + F11. Then it will bring the MS Visual Basic for Applications window. Or you can follow the manual process.
- Go to Developer Tab.
- Select the Visual Basic option.
Step 3: In MS Visual Basic for Applications window insert a go-to Insert option and select Module. Then enter the code in the Module window.
Code
Sub AppendToExistingOnLeft()
Dim a As Range
For Each a In Selection
If a.Value <> "" Then a.Value = "The Total Expenses are $" & a.Value
Next
End Sub
Explanation
- In this line, we are going to iterate using For loop on our selected items.
- For each iteration, we will add a text before the values in our fixed column.
Step 4: Run the code by pressing F5 and see the results on the worksheet.
Note: Add Text using Flash Fill
For adding or combining two or more text in a cell way can use the Flash Fill option 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 will show them in a single column as full names.
Step 1: First enter the full name in cell C4.
Step 2: Now go to the below cell and start typing the Name then it will show auto-suggestion. After that press Enter (you can use keyboard shortcut Ctrl + E).
Step 3: Â All the full names will be shown after pressing Enter automatically.
Related Content: Excel VBA: How to Find and Replace Text in Word Document
Conclusion
These are the ways of adding text before any formula in Excel. I have shown all the methods with their respective examples. If you have any other method of achieving this then please feel free to share it with us.
