Figure 1. Excel DATA VALIDATION Using the ISNUMBER and FIND Function.
If we want to permit entry of data values containing a specified text string only, we are going to make use of a custom data validation formula derived from the FIND and ISNUMBER functions in Excel.
Formula Syntax
=ISNUMBER(FIND("txt",A1))
This formula will force users to only enter a specific text string into the cells of our worksheet.
How to use the Excel DATA VALIDATION with the ISNUMBER and FIND Functions
By using this formula, the FIND function searches for the specified text string and if found, it identifies its location as a number then, based on this number given by the FIND function, the ISNUMBER function returns a TRUE result.
We can get this done in 3 simple steps;
- Arrange the data available for validation and arrange in our worksheet.
See worksheet example below;
Figure 2. Excel DATA VALIDATION Using the ISNUMBER and FIND Function.
Our purpose here is to search the cell range B2:B6 and identity the specific text “SDF”.
Be sure to provide a few empty cells for Excel to return the desired results.
- In our worksheet example above, the Data Validation formula we will type into cell C2 is;
=ISNUMBER(FIND("SDF",B2))
Figure 3. Excel DATA VALIDATION Using the ISNUMBER and FIND Function.
Excel located our specific text string in cell B2 and returned a TRUE result.
- Copy a modified version of the Data Validation formula down in the empty cells of the RESULTS column to get the other results.
Figure 4. Excel DATA VALIDATION Using the ISNUMBER and FIND Function.
Whenever a user adds or changes a cell value, data validation rules are triggered.
Figure 5. Final Result.
Instant Connection to an Expert through our Excelchat Service:
Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.
Leave a Comment