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

Watch Video – Add Text Before a Formula in Excel

The sample dataset contains the Total Expenses for three months, January, February, and March along with First Name and Last Name columns.

The expenses are to be summed and a text added before the formula to indicate what has been done.

Method 1 – Using Ampersand (&) Operator

Steps:

Select cell G5 and enter the formula.

="The Total Expenses of "&B5&C5&" is "&"$"&SUM(D5,E5,F5)

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

Press ENTER and drag the Fill Handle tool down to the other cells.

The results are returned as below.

Adding Text Between Two Formulas

Steps:

Select cell G5 and insert the below formula.

=TEXT(TODAY(), "mmmm dd, yyyy")&" Expenses is $"&SUM(D5, E5, F5)

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

The TEXT function returns the entire output in text format.

Drag the Fill Handle down the column.

Adding a Line Break Between Two Formulas

Steps:

Enter the below formula in cell G5.

=TEXT(TODAY(), "mmmm dd, yyyy")&CHAR(10)&" Expenses is $"&SUM(D5,E5,F5)

Press ENTER.

Method 2 – Using CONCAT Function

Steps:

Enter the below formula in G5.

=CONCAT("The Total Expenses of ",B5, C5," is $",SUM(D5,E5,F5))

This function will add the argument within the parentheses and display the added result.

Press ENTER and drag the Fill Handle tool down to the other cells.

Method 3 – Utilizing CONCATENATE Function

Steps:

Select cell G5 and enter the following formula.

=CONCATENATE("The Total Expenses of ", B5, C5," is $", SUM(D5, E5, F5))

Press ENTER and drag the Fill Handle tool down to the other cells.

The results are returned as below.

Method 4 – Applying TEXTJOIN function

Steps:

Enter the below formula in G5.

=TEXTJOIN("",TRUE,"The Total Expenses of ",B5,C5," is $",SUM(D5,E5,F5))

This function will add the required text before the function.

Press ENTER and drag the Fill Handle tool down to the other cells.

Method 5 – Employing VBA Code

Steps:

The total sum of your dataset, which is calculated with the below formula.

=SUM(D5:F5)

Press ENTER.

Go to the Developer tab >> choose Visual Basic.

A window will appear.
Select the Insert tab >> pick Module >> move to Module1.
Enter the below 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

The result will be displayed in the relevant column.

How to Add Text Using Flash Fill Feature

In this example Flash Fill is used to copy the information from the first two columns in a single column.

Steps:

Enter the Full Name in cell C4.

Go to the Home tab.
Select Fill.
Choose Flash Fill.

The column is filled with the names as below.

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

In this sample datasets the ID numbers are to be updated by entering text in the middle of the existing numbers.

Steps:

Go to cell D5 and insert the below formula.

=LEFT(C5,3)&"M"&MID(C5,4,5)

The LEFT function returns the first three characters of the ID contained in C5, and the MID function returns the 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.