Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Managing Conditional Formatting Rules in Excel
  • Chart Axes in Excel
  • Add Outline to Data in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • Two-column Lookup in Excel
  • How to use Excel VLOOKUP Function
  • How to use Excel MATCH Function
  • Create hyperlink with VLOOKUP in Excel
  • How to get last row in text data in Excel

Data Validations

  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation with conditional list
  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days

Excel Data validation must begin with

by

Using the example below, this tutorial shows how to create Data validation must begin with in Excel.

Formula

=EXACT(LEFT(A1,3),"XX-")
Explanation

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-")

How this formula works

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.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF with wildcards in Excel
  • Invoice status with nested if in Excel
  • OR function Examples in Excel
  • Return blank if in Excel
  • Nested IF function example in Excel

Date Time

  • Display Date is same month in Excel
  • How to calculate Quarter of Date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Dynamic date list in Excel
  • Pad week numbers with zeros in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Map inputs to arbitrary values in Excel
  • Running count group by n size in Excel

General

  • Check if multiple cells have same value with case sensitive in Excel
  • Subtotal invoices by age in Excel
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • Mark Workbook as Final in Excel
  • Convert column letter to number in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning