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

Lookup and Reference Examples

  • How to use Excel MATCH Function
  • Get nth match with INDEX / MATCH in Excel
  • Vlookup Examples in Excel
  • Count rows that contain specific values in Excel
  • How to get last row in text data in Excel

Data Analysis Examples

  • How to Create Column Chart in Excel
  • Create Scatter Chart in Excel
  • Conditional Formatting Rules in Excel
  • How To Create Frequency Distribution in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

Data Validation Examples

  • Data validation must not exist in list
  • How To Create Drop-down List in Excel
  • Excel Data validation unique values only
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only

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:   Check multiple cells are equal 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:   Two ways to Compare Text 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 not equal to 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
  • ASC function: Description, Usage, Syntax, Examples and Explanation
  • How to convert numbers to text using TEXT function in Excel
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Text Functions References and Examples
  • How to pad text to match equal length in Excel
  • Get days, months, and years between dates in Excel
  • Pad week numbers with zeros in Excel
  • Display Date is workday in Excel
  • Count day of week between dates in Excel
  • Convert date to Julian format in Excel
  • DB function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • NPV function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles