Home » Posts

Data validation must begin with

To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: =EXACT(LEFT(C5,3),"MX-")

April 14, 2025 · 1 min · 58 words · Michael Gilbert

Table of Contents

Explanation

Data validation rules are triggered when a user adds or changes a cell value.

EXACT performs a case-sensitive comparison.

If the two text strings match exactly, EXACT returns TRUE and validation will pass.

Excel formula: Data validation must contain specific text

If the match fails, EXACT will return FALSE, and input will fail validation.

For example, =LEFT(“apple”,3) returns “app”.

COUNTIF can be used to count…

Excel formula: Data validation allow numbers only

Excel formula: Data validation allow text only

Excel EXACT function

Excel LEFT function

Excel COUNTIF function

Explanation#

Explanation