Data validation is a feature used to prevent the insertion of a certain type of data in a cell. If you are trying to validate data and want to find only items that begin with a certain string, this article will give you the information you need.
What is Data Validation must begin with function in Excel?
In Excel, the Data Validation function restricts the type of data or value that is entered in a cell. For example, data validation can be used to make sure that a value entered is between the number 1 and 6.
How the Data Validation must begin with function is used in Excel
The Data Validation must begin with the function that only allows the entry of data that begin with a certain text. To allow the values, users can try a custom formula based on the LEFT and EXACT functions.
Formula or Syntax
The Data Validation must begin with function is carried out with the help of custom formula. Its syntax is:
=EXACT (LEFT (text, [num_chars]), text 2)
Parameters or Arguments of Data Validation must begin with function
The parameters that will be passed for the Data Validation are listed:
- Text: Required. In this argument, you will provide the text from which characters are to be extracted.
- Num_chars: Optional. In this section, you can enter the number of characters you want to be extracted. It will start from the left side of the text.
Now, consider the following example to understand the working of Data Validation function:
- At first, you must have some data values. Then select the Data tab and open the Data Validation option from the tools. The Data Validation function triggers when a cell value is changed or added.
Figure 1. Open the Data Validation tab
- Make a table of valid data and then click on the Data Validation button. Then on the Settings tab, choose an option under Allow section:
- Whole number
- Text Length
Choose Custom from the option as the formula will be applicable only with the custom validation.
Figure 2. Select the Custom option
- Under the Data section, the between condition will be specified automatically.
- Now enter the formula as
EXACT(LEFT (C5, 3),“MX-”). The LEFT formula will extract the values of the first 3 characters. And the EXACT function will compare the extracted text to the text hardcoded in the formula.
Figure 3. Apply the formula
- If the two strings match, then the EXACT function will return TRUE value and validation will be passed. If the match fails, then a FALSE value is returned with fail validation.
Remember that EXACT function is case sensitive and it will give an error if not just a single character matches the value.
Note on Usage of Data Validation must begin with function
The notes on usage for data validation must begin with function are:
- The num_chars has a default value of 1 if you don’t enter any value.
- The LEFT function will extract digits as well.
- In the exact function, text 1 and text 2 arguments are passed. In the text 1 argument, we will enter the LEFT function to apply the must begin with criteria.
- The text 1 and text 2 arguments of the EXACT function can be equal.
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.