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. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar