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

Data Analysis

  • Error Bars in Excel
  • How To Compare Two Lists in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to calculate current stock or inventory in Excel
  • How to create Checklist in Excel

References

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to get last column number in range in Excel
  • How to use Excel MMULT Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Vlookup Examples in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation only dates between
  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text
  • Excel Data validation specific characters 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:   Break ties with helper column and COUNTIF 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:   Count Number of Words 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:   Two ways to Compare Text 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:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart in Excel

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

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get first day of previous month in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Excel Date & Time Functions Example
  • Calculate number of hours between two times in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • If cell contains one of many things in Excel
  • Map inputs to arbitrary values in Excel
  • Group arbitrary text values in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Subtotal by color in Excel
  • How to get original price from percentage discount in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • Transpose: Switch ‘Rows to Columns’ or ‘Columns to Rows’ in Excel
  • Freeze and Unfreeze Panes in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning