How to use excel formula IF cell contains text in Excel

August 1, 2022
12.6K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

A free Office suite fully compatible with Microsoft Office

Free Download

Excel has several formulas that can help you use your data in a convenient way. For example, you can get the output based on whether the cell meets certain specifications. One of Excel's most common tasks is to see if a cell has a value based on a particular condition. This value can be text, date, or any other number.

In this article, we are going to show some Excel formulas to return value in another cell if the cell contains the text.

Using IF Function

The IF function is a logical function based on a given statement. An IF statement can have two outcomes. When comparing, the first result is true and the second result is false.

Function Syntax.

=IF (logical_test, [value_if_true], [value_if_false])

Description of syntax

Logical_test is a cost or logical expression that evaluates to TRUE or FALSE.

Value_if_true - [optional] The value to go back while logical_test evaluates to TRUE.

Value_if_false - [optional] The cost to return whilst logical_test evaluates to FALSE.

1.   Select an empty cell.

2.   Enter the components =IF(C5=Dhaka, Delivered, Not Delivered). Where C5 represents the mobile price and is Dhaka City, Delivered manner whether or not the value is True or Not Delivered. This means if the cost is False.

3.   Press Enter.

4.   Use the Fill Handle Tool for getting other cell values.

Using ISNUMBER Function

ISNUMBER function returns TRUE, and FALSE if not. You can use ISNUMBER combined with the SEARCH or FIND function to verify that a cell has the desired value you want.

The syntax of ISNUMBER is

=ISNUMBER (value) 

where value is the input you want to check

The syntax of the SEARCH function is

=SEARCH (find_text, within_text, [start_num]) 

Description of syntax

find_text – The text to find.

within_text – The text to search within.

start_num - [OPTIONAL] The starting position of the text to search for.

1.   Select a blank cell like D5

2.   Type the formula =ISNUMBER (SEARCH (Desktop, A2:A4)) where “some” is the text to find, A2:A4 is the cell range where you want to find the text.

3.   Press Enter.

However, if you are adding a case-sensitive condition, you must use the FIND function with ISNUMBER.

Find function syntax.

=FIND (find_text, within_text, [start_num]) 

Combination of IF-OR/AND-ISNUMBER function

Now if you want to find either “word” or “some”. You can follow the steps below.

Okay, before we get to the point, let's put in a little OR function.

=OR (logical1, [logical2], …)

Description of syntax:

logical1 - The first condition or analytical value to evaluate.

logical2 - [optional] second condition or analytical value to evaluate.

1.   Select a blank cell like A5.

2. Type the formula =IF(OR(ISNUMBER(SEARCH(“WINDOWS”,A5)), ISNUMBER(SEARCH(“DESKTOP,A5)),” AVAILABLE”,”NOT AVAILABLE”). Where A5 is the Lookup value

3.   Press Enter.

Did you study how to use a formula if the cell contains text in excel? To discover more about the functionality of Word documents, Excel spreadsheets, and PowerPoint slides, follow WPS Academy. To edit word, excel, and PowerPoint documents for free, you may also download WPS Office. Get it now! and have a pleasant and straightforward working experience.