How to Add Text in IF Formula in Excel (6 Useful Ways)

While formulas are a great tool to manipulate data in Excel sometimes they can be challenging to understand. To solve this issue, we can add texts to illustrate the formula values and make them easier to interpret. With this in mind, this article demonstrates 6 handy ways to add text in the IF formula in Excel.


Download Practice Workbook

You can download the practice workbook from the link below.


6 Ways to Add Text in IF Formula in Excel 

Let’s say, we have the Report Card shown in the B4:C14 cells. Here, the dataset shows the student Names and their Scores respectively. So, without further delay, let’s see the methods one by one.

Dataset 1

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Adding Text in IF Formula Using Ampersand Operator

We’ll start things off with the most popular way of adding text to a formula. Yes, you’re right, we’ll use the Ampersand (&) operator. So, let’s begin.

Steps:

  • Initially, move to the D5 cell and type in the expression given below.

=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”) → 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

How to Add Text in If Formula Excel Using Ampersand Operator

That’s it you’ve added text in the IF formula. It’s that simple!

Using Ampersand Operator

Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)


Method-2: Using CONCATENATE Function to Add Text in IF Formula in Excel

For those of you who want to utilize Excel functions, you’ll enjoy the following methods. In this method, we’ll apply the CONCATENATE function to merge text strings. So, let’s see the process in detail.

Steps:

  • To begin, go to the D5 cell and enter the expression shown below.

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

In this formula, 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

After completing the above steps, the results should look like the image shown below.

How to Add Text in If Formula Excel Using CONCATENATE Function

Read More: Combine Text and Formula in Excel (4 Simple Ways)


Method-3: Utilizing TEXT Function

For our next method, we’ll employ Excel’s TEXT function to insert text in the IF formula.
Considering the Time Tracker dataset B4:D13 cells. Here, the Names, Entry, and Exit times of the employees are shown and we want to calculate the Work Hour for each employee. So, let’s see the process in detail.

Dataset 2

Steps:

  • Firstly, navigate to the E5 cell and enter the expression below.

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

In this formula, 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 hour is: “&TEXT(IF(D5<>””,D5-C5,””),”h”))&”Hrs” → becomes
    • =B5&(“‘s total work hour is: “&8&”Hrs” → Similar to the previous method, the Ampersand operator joins the string of texts Jules, total work hour is:, and Hrs.
    • Output → Jule’s total work hour is: 8Hrs

How to Add Text in If Formula Excel Using TEXT Function

  • Secondly, copy the formula to the cells below to complete the table as shown in the screenshot below.

How to Add Text in If Formula Excel Using TEXT Function

Read More: How to Add Text to Multiple Cells in Excel (10 Easy Methods)


Similar Readings


Method-4: Applying TEXTJOIN Function to Add Text in IF Formula

Another way to add text to an Excel formula involves using the TEXTJOIN function which combines a list of text strings just like the previous method. So, just follow along.

Steps:

  • At the very beginning, go to the E5 cell, and provide the expression below.

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

In the above formula, the B5 cell refers to the Name (in this case 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

Finally, the completed table should look like the picture shown below.

How to Add Text in If Formula Excel Using TEXTJOIN Function


Method-5: Utilizing Custom Number Format

If using complex formulas doesn’t suit you then you’ve come to the right place. In this method, we’ll use the Custom Number Format and the TEXT function to add text to the formula. Now, allow me to demonstrate the process below.

Steps:

  • Firstly, select the E5 cell and press the CTRL + 1 key on your keyboard.

Using Custom Number Format

This opens the Format Cells wizard.

  • Secondly, click the Number tab >> then, click on Custom >> in the Type field, enter the format “Total Work Hours: “@ >> lastly, press the OK button.

"Total Work Hours: "@

As a note, the At (@) sign represents the placeholder for Numbers when applying Custom Number Formatting.

Using Custom Number Format

  • Thirdly, go to the E5 cell and enter this expression given below.

=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

  • Lastly, copy the formula to the cells below and the results should look like the picture given below.

Using Custom Number Format

Read More: How to Combine Text and Number in Excel (4 Suitable Ways)


Method-6: Applying VBA Code to Add Text in IF Formula in Excel

If you often need to add text in the If formula, then you may consider the VBA code below. It’s simple & easy, just follow along.
Assuming the List of Staffs data shown in the B5:B14 cells below. Here, we have the Names of the staff and we want to add their ID numbers. So, let’s see it in action.

Dataset 3


Step-01: Open Visual Basic Editor

  • Firstly, navigate to the Developer tab >> click the Visual Basic button.

How to Add Text in If Formula Excel Using VBA Code

This opens the Visual Basic Editor in a new window.


Step-02: Insert VBA Code

  • Secondly, go to the Insert tab >> select Module.

How to Add Text in If Formula Excel Using VBA Code

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

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

Code Breakdown:

Now, I will explain the VBA code for adding text in the IF formula. In this case, the code is divided into two steps.

  • In the first portion, the sub-routine is given a name.
  • Next, define the variable i and assign the Range data type.
  • In the later part, 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-03: Running VBA Code

  • Now, close the Visual Basic window and select the C5:C14 range of cells >> click the Macros button at the top.

How to Add Text in If Formula Excel Using VBA Code

This opens the Macros dialog box.

  • Following this, click the Run button.

How to Add Text in If Formula Excel Using VBA Code

Subsequently, 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. Please do it by yourself.

Practice Section


Conclusion

This article provides quick and easy answers on how to add text in IF formula in Excel. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are happy to answer your queries. Keep learning and keep growing!


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo