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.

Dataset 1


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

How to Add Text in If Formula Excel Using Ampersand Operator

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

How to Add Text in If Formula Excel Using Ampersand Operator

You’ve added text in the IF formula.

Using Ampersand Operator

Read More: How to Add Text to Cell Value in Excel


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

How to Add Text in If Formula Excel Using CONCATENATE Function

  • Copy the formula to other cells.

How to Add Text in If Formula Excel Using CONCATENATE Function

Read More: How to Add Text Before a Formula in Excel


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.

Dataset 2

  • 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

How to Add Text in If Formula Excel Using TEXT Function

  • Copy the formula to other cells to complete the table.

How to Add Text in If Formula Excel Using TEXT Function

Read More: How to Add Text to Multiple Cells in Excel


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

How to Add Text in If Formula Excel Using TEXTJOIN Function

  • Copy the formula to other cells to complete the table.

How to Add Text in If Formula Excel Using TEXTJOIN Function


Method 5 – Utilizing Custom Number Format

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

Using Custom Number Format

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.

Using Custom Number Format

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

How to Add Text in If Formula Excel Using Custom Number Format

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

Using Custom Number Format


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.

Dataset 3


Step 1 – Open the Visual Basic Editor

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

How to Add Text in If Formula Excel Using VBA Code


Step 2 – Insert VBA Code

  • Go to the Insert tab and select Module.

How to Add Text in If Formula Excel Using VBA Code

  • 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

How to Add Text in If Formula Excel Using VBA Code

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.

Code Explanation


Step 3 – Running VBA Code

  • Close the Visual Basic window.
  • Select the C5:C14 range of cells.

How to Add Text in If Formula Excel Using VBA Code

  • Click the Macros button and run the AddText macro.

How to Add Text in If Formula Excel Using VBA Code

  • The ID- text will be added before all the numbers in the selected range.

How to Add Text in If Formula Excel Using VBA Code


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side.

Practice Section


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Excel Add Text to Cell Value | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo