How to use excel formula IF cell contains text in Excel
A free Office suite fully compatible with Microsoft Office
A free Office suite fully compatible with Microsoft Office
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.
Was this helpful?
Yes
No
Trending Tutorials
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to color cell based on value in Excel?
- 5. How to compare two excel sheets and highlight differences
- 6. How to add text to beginning or end of all cells in Excel