Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Offset in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to get address of named range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • Extract data with helper column in Excel

Data Analysis Examples

  • How to perform a t-Test in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Excel Pie Chart
  • Excel Bar Chart
  • Conditional Formatting Data bars Examples in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Data validation must not exist in list
  • Excel Data validation must contain specific text
  • Excel Data validation date in specific year
  • Excel Data validation no punctuation

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.

Worked Example:   Count missing values in Excel

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.

Worked Example:   Break ties with helper column and COUNTIF in Excel

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.

Worked Example:   Count cells less than in Excel

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:

Excel Data validation exists in list

Next Post:

Excel Data validation with conditional list

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
  • How to extract name from email address in Excel
  • How to count specific words in a range in Excel
  • How to display conditional message with REPT function in Excel
  • How to split text with delimiter in Excel
  • How to count total characters in a range in Excel
  • Count birthdays by month in Excel
  • Get days between dates ignoring years in Excel
  • Get month name from date in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Get day from date in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles