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

Lookup and Reference Examples

  • Basic INDEX MATCH approximate in Excel
  • Create hyperlink with VLOOKUP in Excel
  • Lookup entire row in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Merge tables with VLOOKUP in Excel

Data Analysis Examples

  • Understanding Pivot Tables in Excel
  • Move chart to a separate worksheet in Excel
  • How to count table columns in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • How to create running total in an Excel Table

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation must begin with
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text

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:   Convert text to date 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:   Highlight cells that end with 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:   Remove unwanted characters 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 count keywords in a range of cell
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • Get fiscal quarter from date in Excel
  • Add business days to date in Excel
  • How to calculate nth day of week in month in Excel
  • How to get year from date in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest for given period in Excel
  • DOLLARDE function: Description, Usage, Syntax, Examples and Explanation
  • PDURATION function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles