Data Validation for Specified Text only

If you want to check if the values that contain a specified text string in one cell, you can use a combination of the FIND function and ISNUMBER function as a formula in the Data Validation.

Data Validation for Specified Text only

​Suppose that you want to check that if the values in column B contain the specified text string “@gmail.com”, IF True, then return TRUE, otherwise, it returns FALSE.

You can use the following formula:

=ISNUMBER(FIND("@gmail.com",B1))

Data Validation for Specified Text only1

In the above formula, the FIND function will locate the position of the searched text string “@gmail.com” in Cell B1. If the text string is searched, then return the starting position of the searched text string in cell B1. If not, it will return the Excel #VALUE! Error.

The returned results of the FIND function will be as the first argument of the ISNUMBER function. So if the FIND function returns the correct numeric position, then the ISNUMBER function returns TRUE. Otherwise, returns FALSE.

Last, you can add the above formula into Data Validation as rules to B1:B3.

Data Validation for Specified Text only1

Data Validation for Specified Text only1

When you adds or change the values in range B1:B3, the DATA validation will be triggered.

Data Validation for Specified Text only1


Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Related Posts
If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How to Count Cells that Contain only numbers in Excel
count cells that contain number2

This post will guide you how to count the number of cells that only contain numbers within a range of cells using a formula in Excel 2013/2016. Count Number of Cells that Contain Numbers Assuming that you have a data ...

How to Count Cells that Contain X or Y in Excel
count cell that contain x or y7

This post will guide you how to count the number of cells that contain X or Y in a given range cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text ...

How to Extract Initials From a Name in Excel
extract initials from a name2

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016. Extract Initials from a Name Using a Formula Extract Initials from a ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

How to Filter Cells Starts with Number or Letter in Excel

This post will guide you how to filter values starts with number or letter in one single cell in Excel. How do I filter in a list those that begin with number or letter using a formula in Excel. Filter ...

How to Ignore Error Values When Calculating the Average in Excel
average cells ignore error3

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!. Assuming that you have a list ...

How to Limit Data Entry in a Cell in Excel
limit data entry in cell8

This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation ...

How to Ignore Blank Cells in a Formula in Excel
ignore blank cells in a formula3

This post will guide you how to ignore blank cells in a formula in Excel. How do I ignore blank cells when performing calculations in a formula in Excel. Ignore Blank Cells in a Formula Video:Ignore Blank Cells in a ...

Sidebar