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.
How to Add Text in IF Formula in Excel: 6 Ways
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.
We have used the Microsoft Excel 365 version here, you can use any other version 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
- Next, use the Fill Handle tool to paste the formula into the cells below.
That’s it you’ve added text in the IF formula. It’s that simple!
Read More: How to Add Text to Cell Value in Excel
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
After completing the above steps, the results should look like the image shown below.
Read More: How to Add Text Before a Formula in Excel
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.
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
- Secondly, copy the formula to the cells below to complete the table as shown in the screenshot below.
Read More: How to Add Text to Multiple Cells in Excel
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
Finally, the completed table should look like the picture shown below.
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.
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.
- 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.
- Lastly, copy the formula to the cells below and the results should look like the picture given below.
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.
Step-01: Open Visual Basic Editor
- Firstly, navigate to the Developer tab >> click the Visual Basic button.
This opens the Visual Basic Editor in a new window.
Step-02: Insert VBA Code
- Secondly, go to the Insert tab >> select Module.
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
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.
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.
This opens the Macros dialog box.
- Following this, click the Run button.
Subsequently, the “ID-” text will be added before all the numbers in the selected range.
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.
Download Practice Workbook
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
- How to Add Text in Excel Spreadsheet
- How to Add Text to Beginning of Cell in Excel
- How to Add Text to Cell Without Deleting in Excel
- How to Add Text in the Middle of a Cell in Excel
- How to Add Text to End of Cell in Excel
- How to Add a Word in All Rows in Excel
- Add Text and Formula in the Same Cell in Excel
<< Go Back to Excel Add Text to Cell Value | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
