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

Check Cell If Contains One of Many with Exclusions

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings? In this article, ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Filter or Remove Columns

Assume that in MS Excel, you have a table consisting of a few columns consisting of few values, and you want to filter to remove the specified columns from the table. You might take it easy and would prefer to ...

Sidebar