Excel is like a magic box filled with tools that help you with numbers and charts. Have you heard the expression "Excel Contains Formula"? It may sound hard, but it's not. In this article, we will tell you what it is and how to do it step by step. Plus, we'll share a great software tool that makes it all easier.
What Is The “If Cell Contains ”Formula In Excel?
The "If Cell Contains" formula in Excel is a logical function used to check whether a specific cell contains a value of interest. This value could be any text or number, specific text, or simply checking if the cell is not empty. The formula typically employs the IF function to perform a logical test, returning one value if the condition is met (i.e., the cell contains the specified value) and another if the condition is not met (i.e., the cell does not contain the specified value).
This formula has several variations, depending on the precise values you want to find, allowing for various tasks like searching with partial matches or testing multiple criteria with OR and AND logic.
How To Create And Use The “If Cell Contains” Formula In Excel?
Step 1: Open the Excel file that contains the addresses.
Step 2: Identify the column (e.g., column A) that contains the addresses you want to test.
Step 3: Select the cell in column B next to the first address you want to check (e.g., cell B2).
Step 4: Start writing the formula by typing =IF(.
Step 5: Introduce an OR function by typing OR(.
Step 6: Use the SEARCH function to search for the first condition, "CB2". Type ISNUMBER(SEARCH("CB2 ", A2)).
Step 7: Type a comma to separate the logical tests within the OR function.
Step 8: Use the SEARCH function again to search for the second condition, "CB3". Type ISNUMBER(SEARCH("CB3 ", A2)).
Step 9: Close the OR function with a parenthesis and type a comma to separate the logical test of the IF function.
Step 10: Type the value to return if the logical test is true. In this case, type "Local".
Step 11: Type a comma and enter the value to return if the logical test is false. You can enter an empty string by typing ".
Step 12: Close the IF function by typing a closing parenthesis.
Step 13: Press Enter to apply the formula to cell B2.
Step 14: Copy the formula to the remaining cells in column B to apply the same logic to all the addresses.
Step 15: Inspect the results in column B to see which addresses are determined as "Local."
These steps create a formula that checks whether the addresses in column A contain "CB2" or "CB3", indicating whether they are considered local. The formula can be adjusted to include additional conditions or change the values returned for true or false.
Example 1: If Cell Contains Any Value, Then Return a Value
This scenario checks whether or not the A2 cell is blank and then returns a specific value depending on the result.
Formula
=IF(A2<>", "No," "")
Result
The formula will return "No" in the output cell if the A2 cell is not blank. If it's blank, the output cell will remain blank.
Example 2: If Cell Contains Text/Number, Then Return a Value (Check for Text)
This formula returns "Yes" if the target A2 cell contains text.
Formula
=IF(ISTEXT(A2), "Yes", "")
Result
Since the A2 cell contains text, the formula will return "Yes" to the output cell.
Example 3: If Cell Contains Specific Text, Then Return a Value
This formula returns "Yes" if the A2 cell contains the specific text "example."
Formula
=IF(A2="example", "Yes," "")
Result
Since the A2 cell consists of the text "example," the formula will return "Yes" to the output cell.
Example 4: If the Cell Contains Specific Text, Then Return a Value (Case-Sensitive)
This case-sensitive version returns "Yes" if the A2 cell contains the exact text "EXAMPLE."
Formula
=IF(EXACT(A2,"EXAMPLE"), "Yes", "")
Result
The formula will return "Yes" to the output cell, where the A2 cell consists of the text "EXAMPLE" and the corresponding case.
Example 5: If the Cell Does Not Contain Specific Text, Then Return a Value
This formula returns "No" if the A2 cell doesn't contain the specific text "example."
Formula
=IF(A2=" example,", "No")
Result
Because the A2 cell consists of the text "example," the formula will return a blank cell. Other cells will return "False" to the output cell.
Example 6: If Cell Contains One of Many Text Strings, Then Return a Value
This formula identifies cells that contain at least one of many words you’re searching for, such as "t-shirt" or "hoodie."
Formula
=IF(OR(ISNUMBER(SEARCH("t-shirt",A2)),ISNUMBER(SEARCH("hoodie",A2))),"Valid ","")
Result
This formula will return the value of "Valid" to the output cell since the A2 cell contains one of the text values searched for.
Example 7: If the Cell Contains Several of Many Text Strings, Then Return a Value
This formula identifies cells containing several words you’re searching for, such as "hoodie" and "black."
Formula
=IF(AND(ISNUMBER(SEARCH("hoodie",A2)),ISNUMBER(SEARCH("black",A2))),"Valid ","")
Result
The formula will return "Valid" in the output cell because the A2 cell contains both text values searched for.
Best Free Alternative To Microsoft Office - WPS Office
WPS Office, developed by Kingsoft, has gained attention as a formidable alternative to Microsoft Office. Its range of features, user-friendly interface, and free availability make it a go-to option for those looking for a capable productivity suite without spending money. Here's a detailed look at why WPS Office is the best free alternative to Microsoft Office.
Cost-Effectiveness
Free Availability
WPS Office provides a comprehensive suite of tools for free for word processing, spreadsheets, and presentations. Unlike Microsoft Office, which requires a subscription for its full-featured version, WPS Office delivers essential functions without costing a dime. It's a boon for individuals, students, and small businesses operating on a tight budget.
Features
Similar to Microsoft Office
WPS Office's interface and functionalities closely resemble those in Microsoft Office. Whether working on a document, crunching numbers in a spreadsheet, or creating a visually appealing presentation, WPS Office provides the necessary tools.
Additional Tools
Beyond the basics, WPS Office also offers additional tools and integrations that enhance its appeal. From PDF conversion to cloud collaboration, WPS Office's feature set is more than enough to meet the demands of everyday tasks.
Compatibility
Cross-Platform Availability
WPS Office is not limited to one particular operating system or device. Whether you're using Windows, Mac, Android, or iOS, WPS Office has you covered. Its cross-platform availability ensures that you can work seamlessly, regardless of your device.
Microsoft Office File Formats
WPS Office supports Microsoft Office file formats, meaning you can open, edit, and save files just like in Microsoft Office. This compatibility eliminates worries about file conversion or compatibility issues when sharing documents with others using Microsoft Office.
FAQs
1. How To Highlight A Row That Contains Specific Text?
To highlight a row that contains specific text, select the range where the text is located, then go to the Home tab and click on Conditional Formatting. You must select "Highlight Cells Rules" before selecting "Text that contains." Select your preferred formatting style, enter the text you want to find, and click OK. It will highlight the entire row containing the specific text.
2. How Do I Extract Specific Contents From A Cell In Excel?
To extract specific contents from a cell in Excel, you can utilize functions like LEFT, RIGHT, and MID to pinpoint text from a cell or combine MID and FIND for more precise extraction. The TRIM function can be used to remove unnecessary spaces. Alternatively, Flash Fill can identify patterns and automatically combine strings into one cell.
Summary
The article provides a detailed guide on using the "Excel Contains Formula" in Excel, offering step-by-step instructions and examples to help users with various logical tasks. From checking specific values to performing complex functions like searching and matching, the article covers different scenarios that can be handled with this versatile formula.
In addition to explaining Excel functions, the article introduces WPS Office, a free alternative to Microsoft Office, including Excel. Highlighting its cost-effectiveness, compatibility with various platforms, and resemblance to Microsoft Office in terms of interface and functionalities, WPS Office is an appealing and robust alternative to MS Excel.
