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.
All articles DATA VALIDATION Data validation must contain specific text

Data validation must contain specific text

Data Validation is a feature in Microsoft Excel which restricts the values or type of data that users enter into a cell.  It automatically checks whether the value entered is allowed based on the specified criteria. This step by step tutorial will assist all levels of Excel users in allowing only values containing specific text by using Data Validation.

Figure 1. Final result: Data validation must contain specific text

Working formula: =ISNUMBER(FIND(".jpg",C3))

Syntax of ISNUMBER Function

ISNUMBER function tests if a value refers to a number and returns TRUE; otherwise it returns FALSE

=ISNUMBER(value)

  • value – the value that we want to test;  value argument can be a blank or empty cell, error or logical value, text, date, number or a cell reference

Syntax of FIND function

FIND locates and returns the starting position of a substring within a text string; FIND is case-sensitive

=FIND(find_text, within_text, [start_num])

The parameters are:

  • find_text – the text we want to find in the second text string    
  • within_text  the text string containing the text we want to find    
  • start_numOptional; the starting character where we want to start the search; if omitted, default value is 1, which is the first character of within_text  

Setting up Our Data

Our table consists of two columns: Image No. (column B) and Filename (column C).  We want to restrict the filename entered in column C and allow only values that contain the specific text “.jpg”.  We can do this by applying Data Validation.

Figure 2. Sample data: Data validation must contain specific text

Allow only values that contain specific text “.jpg”

We want to restrict the filenames that we input in column C to values that contain the specific text “.jpg”.   We can do this with Data Validation by following these steps:

Step 1.  Select the cells whose values we want to restrict.  In this case, select cells C3:C7

Step 2.  Click the Data tab, then the Data Validation menu and select Data Validation

Figure 3.  Selecting Data Validation

The Data Validation dialog box will pop up.  

Figure 4.  Data Validation preview

Step 3.  Click the Allow: drop-down button and select Custom as Validation criteria

Step 4.   Enter the following in the Formula bar:  =ISNUMBER(FIND(“.jpg”,C3))

Figure 5.  Creating a data validation rule

ISNUMBER returns TRUE for numeric values and FALSE if otherwise.  The argument inside the ISNUMBER is the FIND function, which searches cell C3 for the specific text string “.jpg”and returns a numeric value if “.jpg” is found.  

For every filename entered in column C that contains the text “.jpg”, ISNUMBER returns TRUE, Data Validation passes and the value is allowed.  Otherwise, Data Validation fails and the value is restricted.

Step 5.   Click OK

We have now restricted the values in C3:C7 to allow only the values that contain the specific text “.jpg” through Data Validation.  Let us try and enter “Sun.jpg” into cell C3.  

Figure 6.  Value containing “.jpg” allowed by Data Validation

The filename “Sun.jpg” is allowed because it contains the specific text “.jpg” as entered in the Data Validation rule.  Now let’s input “Moon.png” into cell C4.

Figure 7.  “Moon.png” restricted by Data Validation

The filename “Moon.png” does not contain “.jpg”, hence Data Validation has restricted the input of “Moon.png” into cell C4.  For restricted values, Excel shows a default warning message that says:

“The value you entered is not valid.  A user has restricted values that can be entered into this cell.”  

We are then presented with three options: Retry, Cancel or seek Help.  

Data Validation is very accurate in allowing only the values as specified in the formula or validation criteria.  See below table that is filled up with filenames containing the specific text “.jpg” as allowed by Data Validation.  

Figure 8.  Output: Data Validation must contain specific text

Note

We can also use Data Validation to allow only values that does not contain specific text.  In the validation rule, we can use the following formula where we replace the ISNUMBER function with ISERROR.  

=ISERROR(FIND(".jpg",C3))

This way, cell C3 will pass validation only if “.jpg” is not found.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution 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:

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