How to Add Text in an IF Formula in Excel (6 Methods)

Dataset Overview

Let’s consider a Report Card dataset displayed in cells B4:C14. This dataset contains student names and their scores.

Method 1 – Using the Ampersand Operator

Select cell D5.
Enter the following formula:

=B5&" "&IF(C5>=65,"passed","failed")&" "&"the test"

Here, B5 represents Adam, and C5 indicates his score (which is 68).

Formula Breakdown

IF(C5>=65,”passed”,”failed”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, C5>=65 is the logical_test argument which compares the value of the C5 cell with 65. If this value is greater than or equal to 65 then the function returns passed (value_if_true argument) otherwise it returns failed (value_if_false argument).
- Output → passed
=B5&” “&IF(C5>=65,”passed”,”failed”)&” “&”the test” → becomes
- =B5&” “&passed&” “&”the test” → The Ampersand operator joins the string of texts Adam, passed and the test. Moreover the ” ” is the White Space character in between each word.
- Output → Adam passed the test

Use the Fill Handle tool to copy the formula to other cells.

You’ve added text in the IF formula.

Method 2 – Using the CONCATENATE Function

Go to cell D5.
Enter the following formula:

=CONCATENATE(B5," ",IF(C5>=65,"passed","failed")," ", "the test")

Here, the B5 cell refers to Adam while the C5 cell indicates his Score which is 68.

Formula Breakdown

IF(C5>=65,”passed”,”failed”)
- Output → passed
=CONCATENATE(B5,” “,IF(C5>=65,”passed”,”failed”),” “, “the test”) → becomes
- =CONCATENATE(B5,” “, passed,” “, “the test”) → The CONCATENATE function combines the texts Adam, passed, and the test.
- Output → Adam passed the test

Copy the formula to other cells.

Method 3 – Utilizing the TEXT Function

Consider the Time Tracker dataset in cells B4:D13, which includes employee names, entry times, and exit times. We want to calculate the Work Hours for each employee.

Go to cell E5.
Enter the following formula:

=B5&("'s total work hours are: "&TEXT(IF(D5<>"",D5-C5,""),"h"))&"Hrs"

Here, the B5 cell refers to the Name (here it is Jules) while the C5 and D5 cells represent the Entry and Exit times respectively.

Formula Breakdown

IF(D5<>””, D5-C5,””) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, D5<>”” is the logical_test argument that checks if the D5 cell is blank. If blank, then the function performs D5 – C5 (value_if_true argument) else it returns “” (value_if_false argument).
- Output → 0.3333
TEXT(IF(D5<>””,D5-C5,””),”h”)) → becomes
- TEXT(0.3333,”h”)) → converts a value to text in a specific number format. In this formula, 0.3333 is the value argument from the IF function while “h” is the format_text argument that formats the value as Time.
- Output → 8
=B5&(“‘s total work hours are: “&TEXT(IF(D5<>””,D5-C5,””),”h”))&”Hrs” → becomes:
- =B5&(“‘s total work hours are: “&8&”Hrs” → Similar to the previous method, the Ampersand operator joins the string of texts Jules, total work hours are:, and Hrs.
- Output → Jule’s total work hours are: 8Hrs

Copy the formula to other cells to complete the table.

Method 4 – Using the TEXTJOIN Function

Go to cell E5.
Enter the following formula:

=TEXTJOIN(" ",TRUE,B5,"worked for",TEXT(IF(D5<>"",D5-C5,""),"h"),"Hrs")

In the above formula, the B5 represents the employee’s name (e.g., Jules) while the C5 and D5 cells represent the Entry and Exit times, respectively.

Formula Breakdown

=TEXTJOIN(” “,TRUE,B5,”worked for”,TEXT(IF(D5<>””,D5-C5,””),”h”),”Hrs”) → becomes

=TEXTJOIN(” “,TRUE,B5,”worked for”,8Hrs) → concatenates a range of text string with a delimiter. Here, “ ” is the delimiter argument which is the White Space Next, TRUE is the ignore_empty argument which ignores empty cells. Lastly, the B5,”worked for”,8Hrs are the text1, text2, and text3 arguments respectively.
- Output → Jule’s worked for 8Hrs

Copy the formula to other cells to complete the table.

Method 5 – Utilizing Custom Number Format

Select cell E5 and press CTRL + 1 on your keyboard.

This opens the Format Cells wizard.

Click the Number tab, then select Custom.
In the Type field, enter the format “Total Work Hours: “@.
- The @ sign represents the placeholder for Numbers in Custom Number Formatting.
Press OK.

Go to cell E5 and enter the following formula:

=TEXT(IF(D5<>"",D5-C5,""),"h")

Here, the C5 and D5 cells represent the Entry and Exit times respectively.

Copy the formula to the other cells and the results should look like the picture given below.

Method 6 – Applying VBA Code to Add Text in IF Formula

Assuming the List of Staffs data is in cells B5:B14. We have the Names of the staff, and we want to add their ID numbers.

Step 1 – Open the Visual Basic Editor

Navigate to the Developer tab and click the Visual Basic button.

Step 2 – Insert VBA Code

Go to the Insert tab and select Module.

Paste the following code:

Sub AddText()
Dim i As Range
For Each i In Selection
If i.Value <> "" Then i.Value = "ID- " & i.Value
Next
End Sub

This code adds ID- before each value in the selected range (e.g., ID-123).

Code Breakdown

The code is divided into two steps.

In the first portion, the sub-routine is given a name.
Define the variable i and assign the Range data type.
Use the IF statement and the Selection property to iterate through each value in the selected range of cells.
If any cell of this range contains a value, then the IF statement appends the “ID-” text before it. Otherwise, the cell is left blank.