Go Back

Learn How to Perform Excel DATA VALIDATION Using the ISNUMBER and FIND Function

While entering data if you want to force users to insert specific text string in cells then you need to rely on Data Validation to check for specific text in Excel. This can be achieved using custom formulas in Data Validation that use the ISNUMBER and FIND functions.

Performing Data Validation in Excel

Entry of specific text in cells could be case-sensitive or case-insensitive. If specific text is case sensitive, then the custom formula would be based on the ISNUMBER and FIND functions in Data Validation to check for certain text. But if specific text entry is case-insensitive, then the custom formula would be based on ISNUMBER and SEARCH functions in Data Validation to check for specific text.

Case-sensitive Specific Text

Suppose you want to enter values of serial numbers in cells range A2:A10 and you want to ensure that serial number should contain the specific word “PS” in case-sensitive format, then you need to use the following custom formula in Data Validation to check for specific text in Excel. You need to select cells A2:A10 and enter the following formula in the Data Validation custom formula bar.

=ISNUMBER(FIND("PS",A2))

In this formula, FIND function checks the specific text “PS” in cells A2:A10 and returns its position as a number if found, and ISNUMBER function returns TRUE based on the numeric value returned by FIND function. In this way, Excel makes it possible for users to enter the serial number in cells range A2:A10 that contain specific text “PS” using Data Validation to check for specific text in case-sensitive format.

String starting with Case-sensitive Specific Text

Suppose you need to restrict the entry of a serial number starting with the  case-sensitive specific text “PS-“ in cells A2:A10, then following custom formula will be used in Data Validation to check for specific text at the start of string;

=FIND("PS-",A2)=1

This custom formula in Data Validation allows users to enter serial number starting with “PS-“ where FIND function returns the position of case-sensitive specific text “PS-“ as 1.

Case-insensitive Specific Text

If you want to restrict entering values in cells containing case-insensitive specific text, then you need to replace FIND function with SEARCH function, combined with ISNUMBER function in the custom formula of Data Validation to check for specific text in Excel, such as;

=ISNUMBER(SEARCH("PS",A2))

This formula will ensure whether the cell contains specific text “PS” in any of the formats, lower, upper or proper case, anywhere in cell’s value as shown below.

 

 

String starting with Case-insensitive Specific Text

For entering values in cells A2:A10 starting with case-insensitive specific text, you need to enter a custom formula in Data Validation to check for specific text at the start of the value.

Suppose you want that serial number should start with specific text like “PS” in lower, or upper or proper case format then you need to use the following custom formula in Data Validation to check for specific text at the start of the serial number.

=SEARCH("PS",A2)=1

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to check if some specific text is present in particular column or not.
Solved by M. S. in 17 mins
Need to count a Specific text for specific name in a range
Solved by V. B. in 11 mins
Help! I can't figure out what I'm doing wrong! I need to check a column from worksheet 1 for specific text. If it is true, I need to copy the line of cells to worksheet 2
Solved by E. D. in 23 mins

Leave a Comment

avatar