Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Excel DATA VALIDATION Using the ISNUMBER and FIND Function

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;

  1. 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.

  1. 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.

  1. 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.

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

Another blog reader asked this question today on Excelchat:
Solution examples
I want to be able to alter the appearance (font color and fill) of a column of numbers using conditional formatting, based on being greater than or less than the numbers from another column. I am using Excel 2016. I am unable to get any formula to work. Please help!
Solved by I. J. in 59 mins
I need assistance with an excel formula. I have a 12 month sheet that has a summary page on the 13 page. the monthly totals changes so on the summary page I want to sum the difference - eg. if Jan total is 1500, feb total is 1000, mar total is 800, I want the summary page to show the current amount of 800 when you place the formula to capture all months---showing the diminishing value rather than the sum of all the pages
Solved by E. L. in 60 mins
I need a range of numbers to become the number in the next column
Solved by D. F. in 47 mins
I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc