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_num – Optional; 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.
Leave a Comment