Excel formula if cell contains specific text. However, see below for a case-sensitive option.

Excel formula if cell contains specific text Learn how to use ISNUMBER, SEARCH, FIND and IF functions to check if a cell contains specific text in Excel. For example, if we are testing for the word "green" and the text contains the word "evergreen" but not the word "green" the formula should return FALSE. In Excel, just like using a formula, you can use conditional formatting to highlight a cell if it contains a specific text. The steps below will walk through the process. We will use the combination of IF, ISNUMBER and SEARCH functions in Microsoft Excel. In the appendix of file, the column A is the original list, and I want to have it return "good" in B2 if A2 contains "apple", and return "nice" in B3 if A3 contains "help". I tried the IF formula on the Feb 6, 2025 · Checking if a cell contains specific text is a fundamental skill in Excel for anyone who needs to categorize, filter, or analyze data. See examples, explanations and tips for case-sensitive and case-insensitive searches. Compare one cell to another cell To do this task, use the EXACT function. It allows you to evaluate the contents of a cell and perform actions or return values based on given conditions. Note the formula is using a wildcard (*) and Jun 22, 2023 · This tutorial explains how to use INDEX MATCH if cell contains text in Excel, including an example. This formula allows you to look for partial text matches inside a cell. Apr 17, 2025 · Learn how to return values in another cell in Excel based on specific text in a cell. Jun 21, 2024 · This Excel formula will help you to check if a Cell contains a Number, then return specific value or preform some other calculations. Microsoft Excel offers powerful methods to check and analyze cell contents, from verifying data to searching for specific strings and meeting criteria. Includes images and detailed explanations. The formula in F5 is: Aug 30, 2024 · Microsoft Excel is a powerful tool for data management, analysis, and reporting. See examples, explanations, and tips for different scenarios and functions. Master flexible conditional logic Aug 12, 2024 · Learn easy Excel formulas to check if a cell contains specific text. There are two ways to do this: Pre-Defined Option or Custom Formula. See full list on exceljet. To allow data entry only if a cell contains specific text, we use DATA validation with Custom formula. 👍Solution using standard Excel functions: https://y Aug 15, 2024 · Hello. Learn how to use the IF, SEARCH, and ISNUMBER functions to check if a cell contains specific text, regardless of upper- or lowercase. Jun 29, 2021 · Method 4 – Use an INDEX-MATCH Formula to Return a Value If a Cell Contains Specific Text We have fetched the cell values from the LIST column where they matched with the Product and showed them in the result column. Although there isn't a dedicated in-built Excel formula, there have 3 different ways that can be used to check what a cell contains - Jun 15, 2024 · 5 quick methods to sum if cell contains text in another cell in Excel. This example used IF and COUNTIF functions to check for a value in a Cell and return required value. Dec 14, 2023 · To test for cells that contain specific text, you can use a formula based on the IF function combined with the SEARCH and ISNUMBER functions. Nov 28, 2018 · Easily sum cells containing specific text for a partial match in Excel using the SUMIF or SUMIFS function with a wildcard. Discover multiple methods using formulas or Kutools for Excel. Most users will think first of the IF function. One common task users face is searching for specific text within a cell. May 25, 2023 · Learn how to test if a cell contains specific text using an Excel Formula. If YES, column B would be a blank cell. This is a useful skill for anyone working with large data sets in Excel, as it allows you to quickly and easily identify and filter out specific information. Aug 16, 2025 · In Excel, you may often need to verify whether a cell contains any value from a predefined list of items. Sep 1, 2023 · This tutorial explains how to check if a cell contains specific text in Excel and return a value, including several examples. In the example shown, the formula in cell F5 is: =SUMIFS(C5:C16,B5:B16,"*hoodie*") This formula sums the quantity in column C when the text in column B contains "hoodie". Dec 16, 2022 · To sum if cells contain specific text, you can use the SUMIFS or SUMIF function with a wildcard. As the formula is copied down, it returns a sum for each state in column E. For example, assume you have a simple table of data in B4:E11 and you want to highlight all rows that Count if cell contains text with our expert Excel tips! Master functions like SUMPRODUCT COUNTIF to count cells with text quickly and accurately. Jul 22, 2025 · Check if a cell contains one of several values from a list with formulas To determine if a cell contains any of the text values from another range, you can use an array formula that works efficiently even for large datasets. Wildcard characters help you define the partial text, then COUNTIF checks for partial value, and IF returns the result according to that. Used COUNTIF, MATCH, SEARCH, SUM, COUNT, FIND, SUMPRODUCT, ISNUMBER functions. The formula used to test if a cell contains text and return a specified value can be driven by multiple methods with the use of an Excel IF function combined with a COUNTIF, ISNUMBER or ISTEXT function. To check if a cell contains a partial text in Excel, you need to create a formula, with the help of IF, COUNTIF, and wildcard characters. What is the formula I should use to find cells in column A that contain 'accounts'. Insert the following function: Nov 9, 2021 · I am working in an excel sheet where I need to identify specific cells based on the following criteria: If column A = 'Meeting' AND column B contains 'meeting' BUT column B does not contain 'email' Jun 12, 2020 · Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. May 8, 2024 · Here, we learned Excel if cell begins with certain text then return value and covered every possible way to solve this issue. These examples use Excel formulas like SUMIF, FIND, LEFT, and so forth. With just a few steps, you’ll be able to identify and highlight cells containing your desired text. Jun 16, 2024 · Method 10 – Highlight a Cell If It Contains Specific Text in Excel We want to highlight a cell that contains “ Bars ”. May 13, 2025 · Learn how to use a powerful LAMBDA function, TEXTMATCH, to check if a cell contains specific text (exact match or partial). Aug 1, 2023 · This tutorial will demonstrate how to highlight cells if they contain specific text using Conditional Formatting in Excel and Google Sheets. Discover how to use MATCH, SEARCH, and ISNUMBER functions with practical formula examples. Feb 26, 2025 · Learn how to count cells with specific text using Excel formulas like COUNTIF & COUNTIFS to count cells containing or matching certain text. Apr 19, 2024 · Learn 5 super easy methods to check if a cell contains partial text in Excel. Go to the Home tab. Note that SUMIFS is not case-sensitive. However, one problem with IF is that it does not support wildcards like "?" and "*". Jul 31, 2024 · If you need to check if a cell contains a string in Excel, it’s simpler than you might think! By using Excel functions, you can easily determine whether a specific text string is present within any given cell. Mar 14, 2023 · The tutorial shows how to count the number of cells with specific text in Excel. Aug 7, 2017 · Here is the Excel formula if cell contains text then return value in another cell. Jun 15, 2024 · The article evaluates 5 examples of sum if a cell contains criteria. In the example shown, the formula in E5 is: =COUNTIF(B5:B15,"*a*") The result is 6, since there are six cells in B5:B15 that contain the letter "a". Nov 14, 2023 · The objective of this tutorial is to learn how to write formulas in Excel that can identify specific text strings within cells. If A1 or B1 contain "text value 1" return C1. This technique works in Jul 6, 2024 · 7 Methods to Highlight Cells that Contain Text from a List. Jun 15, 2024 · Learn how to highlight a row if any cell contains any text by using the FIND, and SEARCH functions, and making a drop-down list in Excel. Nov 16, 2021 · I'm trying to figure out how to perform this calculation. We can apply this to copy specific text in another location. Aug 17, 2025 · To sum values when a cell contains specific text in Excel, use the SUMIF function with wildcards. Excel is a versatile spreadsheet software used in various industries and professions for data storage, management, and analysis. Select Conditional Formatting and choose New Rule. This is an array formula and must be entered with Control + Shift Jan 26, 2022 · This tutorial explains how to check if a cell contains text from a list in Excel, including an example. In the example shown, the formula in C5 is: {=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0))} where things (E5:E8 ) and results (F5:F8) are named ranges. In this video we use the IF function of Excel to test if a cell contains specific text. Nov 10, 2020 · Following is the Excel formula to find If Cell Contains Specific Text Then Return Value. Find if a cell or range of cells contains a specific value in Excel. If NO, A would repeat in column B. Excel will only return this value if the ISTEXT function returns TRUE. Check if a cell has any text in it To do this task, use the ISTEXT function. Jul 4, 2024 · 2 suitable uses of VLOOKUP function if a cell contains a word within text in Excel. Oct 22, 2024 · Use formulas in Excel to check if a cell contains specific text, allowing for quick data validation and content identification in large datasets. This guide will explore key functions to check if cell contains text in Excel. Note: The SEARCH function is case-insensitive. Download our Excel workbook, modify data & exercise! Apr 10, 2025 · Learn how to use the Excel ISTEXT function to check if a cell contains text. Download the workbook, practice, and learn them. Jan 19, 2023 · This guide will walk you through two different methods for using Excel functions to determine if a cell or range of cells contains a specific string of text. To test if a cell contains text and return a specified value you can apply Excel or VBA methods. How to Check If Cell Contains Specific Text in Excel In this article, we will learn How to Check If Cell Contains Specific Text in Excel. Aug 29, 2024 · I want Excel to return the text value itself, so I have specified the cell (A2) that contains the value as the value_if_true. Understand its syntax, see practical examples, and discover its applications in data validation and conditional logic. Traditionally, this has been a difficult problem in How to lookup cells having certain text and returns the Certain Text in Excel : find cells if cell contains certain text and returns required results using the IF function in Excel. Select the entire dataset. In this tutorial, we will learn to sum values if a cell contains a specific text. I think you must have thought to do it manually but time constraint. Jul 21, 2025 · Unlock Excel's IF Cell Contains power! Get 25 real-world formulas & examples to check for partial text, multiple words, numbers, & more. Check if a cell matches specific text Use the IF function to return results for the condition that you specify. If the count is greater than zero, then return the corresponding value. This means we can't use IF by itself to test for a substring like "abc" or "xyz" that might appear anywhere in a cell. Scenario : Identify particular text in a cell or different word in given cells. This is particularly helpful for validating user inputs, flagging specific content in datasets, or filtering rows based on keywords. This combination of functions works perfectly. To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. May 10, 2024 · This article will enlighten you with 7 different ways including a VBA code for Excel if cell contains text then copy to another sheet. Select Use a formula to determine which cells to format under the Select a Rule Type section from the New Formatting Rule window. The ISNUMBER functions is used to check for a numeric value and the SEARCH function returns the location of a substring in a string. Aug 17, 2023 · This tutorial explains how to replace text in Excel if a cell contains specific text, including an example. Nov 13, 2023 · In the coming sections, we'll show you how to use the wildcard characters (the asterisk and question mark) within the COUNTIF function to check if a cell contains certain text. Learn them, download the workbook and practice. Apr 30, 2024 · You can return true if the cell contains text in Excel following these 8 ways. May 23, 2023 · The tutorial explains the syntax and basic uses of the IF function in Excel. In the example shown, the formula in C5 is: =IF(ISNUMBER(SEARCH("abc",B5)),B5,"") To test for "if cell equals" you can use a simpler formula. Apr 19, 2024 · Method 2 – Applying Excel SUMIFS Function to Add Up Data If Cell Contains a Specific Text Steps: Select cell C15. Jul 2, 2024 · In this article, you will find 9 easy ways to check if a cell contains text then return value using different functions in Excel. Notice the emphasis here is on words, not substrings. I want to find a formula that will give me two options: - if all my cells have the value Approved then I want to have the value Approved returned and - if one cell contains the value Amend Required and the rest are showing Approved I want to have the value Amend Required returned. The remaining examples show formulas that check if a cell contains text and also identify which text the cell contains. However, see below for a case-sensitive option. Find cells that contain text, numbers, or formulas with the Find and Filter tools. Apr 14, 2025 · Learn how to check if a cell contains any text using Excel formulas like IF, ISTEXT, LEN, and COUNTIF. Let us say, we have input data in Cell A1 and We want to Return Value in Another Cell B1. Aug 5, 2024 · I would gladly know how can I use a formula in Excel so that when in a certain column, a certain number is displayed, then this number is matched to a certain text that is displayed in a Excel row? For instance: if cell A1 = 1; then I want the value:… Jan 30, 2025 · This article demonstrates several techniques to check if a cell contains text based on a list. Nov 9, 2024 · Today’s tutorial will show you how to find if a cell contains specific text using the ISNUMBER and SEARCH functions. Feb 15, 2022 · This tutorial explains how to apply conditional formatting to cells that contain specific text in Excel, including an example. May 13, 2024 · To highlight rows in a table that contain specific text, you can use conditional formatting with a formula that returns TRUE when the text is found. Jun 23, 2024 · This article shows 4 easy ways to find if a range of cells contains specific text in Excel. May 10, 2024 · In this example, the goal is to test the text in a cell and return TRUE if it contains one or more specific words, and FALSE if not. . We can check IF A CELL CONTAINS a specific term in a set of data with a combination of the IF, SEARCH and ISNUMBER functions. We will cover case-sensitive and case-insensitive formula options, along with a Sep 4, 2013 · Is there an in-built function to check if a cell contains a given character/substring? It would mean you can apply textual functions like Left / Right / Mid on a conditional basis without throwing errors when delimiting characters are absent. Search, Find if cell contains specific text or string in a Cell. Mar 14, 2023 · The tutorial shows how to make Excel IF statement with wildcard text and get it to work for partial matches. You will learn how to create an 'If then' formula for text, numbers, dates, blank cells as well as how to write multiple IF statements. Whether you're checking for a keyword in customer feedback or flagging specific product types in an inventory list, this guide will walk you through the most effective methods. net Learn how to use Excel formulas to check if a cell contains any value, text, number, or specific text. Download the workbook and modify the data to find new results. Feb 13, 2023 · To filter data to include data based on a "contains specific text" logic, you can use the FILTER function with help from the ISNUMBER function and SEARCH function. See how to apply Excel conditional formatting, copy or delete rows based on cell value. May 21, 2024 · The goal is to do something if a cell contains one substring or another. Includes examples, VBA code, and a downloadable file. Excel file is freely available to download for you. Step 5) Write the value you want returned if the cell doesn’t contain text as the value_if_false. There are several ways to check if a cell contains text and the case of the text matters to you. Whether you’re managing a large dataset or working with a simple spreadsheet, understanding how to search for text in a cell can streamline your work and help you gain insights quickly. Dec 21, 2022 · I have a column that can show two different statuses, Approved and Amend Required. In this article, we’ll explore Excel formulas that In this article we will learn how to specified number, if a cell contains certain text. The formula is A2=text. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. For example finding the department ID from a database. The trick is to concatenate (glue together) the columns you want to search and to lock the column references so that only the rows can change. The result is 5 since five cells in B5:B15 do not contain the letter "a". It will Check any Cell in a Range for a specific text and return True or False. In the example shown the formula in cell F5 is: =SUMIFS(data[Amount],data[Location],"*, "&E5&" *") Where data is an Excel Table in the range B5:C16. Oct 23, 2023 · Explanation The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2. Sep 12, 2023 · Examples of Excel formula to sum or count if cell contains any text or specific text. Insert the following formula in the Format Apr 16, 2024 · Method 2 – If Cell Contains Specific Text Then Add Text in Another Cell Using IF Function Suppose you want to look for a text value “shirt” in the Product Name column. Jan 10, 2023 · This tutorial explains how to use the IF function in Excel with text values, including several examples. Aug 17, 2022 · To count cells that contain certain text, you can use the COUNTIF function with a wildcard. This method can be used on individual cells when you want to "mark" them or to find out how many cells in a range contain that specific value. Aug 6, 2024 · Here, you will find 7 ways to check if a cell contains text and then return value with multiple conditions in Excel using different functions. In the example shown, the formula in E5 is: =COUNTIF(data,"*a*") In this formula, data is the named range B5:B15. In the example shown, the formula in F5 is: =FILTER(B5:D14,ISNUMBER(SEARCH("rd",B5:B14)),"No results") Which retrieves data where the street column contains "rd". Nov 9, 2021 · To test a cell for one of several strings, and return a custom result for the first match found, you can use an INDEX / MATCH formula based on the SEARCH function. Nov 25, 2024 · To filter data if a cell contains specific text in Excel, you can use a formula based on the FILTER, ISNUMBER, and SEARCH functions. Mastering these techniques is essential for efficient data analysis and management. Among its multitude of applications, one of the common tasks is to identify cells that contains specific text. This allows you to create a chain of checks, with each condition leading to a specific result. For this, you can use Excel’s SUMIF function. It will Validated data after checking A2. Jun 3, 2024 · Learn to use wildcards in Excel's IF Function for partial text matches, making it easy to check if a cell contains a specific word. One option (seen in the example) is to create a logical test with Let's say you want to find text that begins with a standard company prefix, such as ID_ or EMP-, and this text must be in uppercase letters. The first example shows how to check if text in the list is in the cell. How to check if a cell contains a specific text string using the IF + SEARCH Function Let us use the combination of the IF and SEARCH functions in Excel to find a text string within multiple cells of the column. Check if part of a cell matches specific text To do this task, use the IF, SEARCH, and ISNUMBER functions. While Excel doesn’t offer a built-in function that directly checks if a cell contains any item from a list, you can achieve it using formulas based on Mar 21, 2021 · To determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function and wildcards. Apr 6, 2019 · Excel if cell contains text formula to Check if contains excel. Usually with the IF function you test a cell for a complete match, but Aug 24, 2021 · HOW TO: "If cell contains specific text then return specific text" If I have a list of things, and I want to have the return from another list if it contains certain keywords. This article demonstrates different formulas for different Excel versions. Use the COUNTIF function to count how many times a certain text appears in a cell. Feb 10, 2025 · To count cells that do not contain certain text, you can use the COUNTIF function with a wildcard. Input the following formula: Jan 18, 2024 · To evaluate multiple conditions in a cell, you can use nested "if cell contains" statements. However, if you want more flexibility, you can use your own formula, as explained in this article. Nov 10, 2020 · Excel Formula to check if a Range Contains specific text and return a value. Formula examples for exact match, partial match and filtered cells. Aug 13, 2024 · To sum numbers if cells contain text in another cell, you can use the SUMIFS function or the SUMIF function with a wildcard. We will start by using the SEARCH function to find cells that contain a specific word, then convert the results into TRUE / FALSE values, and finally apply conditional formatting to highlight these cells. Figure 1 – Result of using the “if a cell contains” formula General Formula IF(ISNUMBER(SEARCH"abc",cell_num,[value_if_TRUE],[value_if_FALSE]) Formula =IF Feb 7, 2024 · The "IF cell contains" formula in Excel is a powerful statement for categorizing and manipulating data based on specific criteria. This method is especially useful when you need results as logical values (True / False) for use in further formulas, filtering, or logical tests. Oct 17, 2023 · A number of 'Excel if cells contains' formula examples show how to return some value in another column if a target cell contains specific text, any text, any number or any value at all (not empty cell), test multiple criteria with OR as well as AND logic. Jun 30, 2024 · Method 1 – Using the COUNTIF Function to Count If a Cell Contains Any Text in Excel Steps: Select Cell D17 to store the count result. Feb 26, 2024 · In this video, I'll show you how to use the Excel IF function to test IF a cell contains specific text. xfnij hdzklpq mpizb dxyj lneijz ffxhpjo fouht gdwuxb amkjj pzfruqe khopi wdz ihaz fnizfmqv yzrhyd