Explanation
Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to compare the extracted text to the text hard-coded into the formula, "MX-". EXACT performs a case-sensitive comparison. If the two text strings match exactly, EXACT returns TRUE and validation will pass. If the match fails, EXACT will return FALSE, and input will fail validation.
Non case-sensitive test with COUNTIF
If you don't need a case-sensitive test, you can use a simpler formula based on the COUNTIF function with a wildcard:
=COUNTIF(C5,"MX-*")
The asterisk (*) is a wildcard that matches one or more characters.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.
Related formulas
Data validation must contain specific text
Data validation allow numbers only
Data validation allow text only
Related functions
EXACT Function
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.
LEFT Function
The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, =LEFT("apple",3) returns "app".
COUNTIF Function
The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
