Excel's Custom Data Validation: A Trick You Didn't Know You Needed

Credit: Lucas Gouveia/How-To Geek | Darko 1981/Shutterstock

Published Oct 20, 2025

Tony Phillips is an experienced Microsoft Office user with a dual-honors degree in Linguistics and Hispanic Studies. Prior to starting with How-to Geek in January 2024, he worked as a document producer, data manager, and content creator for over ten years, and loves making spreadsheets and documents in his spare time.

Tony is also an academic proofreader, experienced in reading, editing, and formatting over 3 million words of personal statements, resumes, reference letters, research proposals, and dissertations. Before joining How-To Geek, Tony formatted and wrote documents for legal firms, including contracts, Wills, and Powers of Attorney.

Tony is obsessed with Microsoft Office! He will find any reason to create a spreadsheet, exploring ways to add complex formulas and discover new ways to make data tick. He also takes pride in producing Word documents that look the part. He has worked as a data manager in a secondary school in the UK and has years of experience in the classroom with Microsoft PowerPoint. He loves to encounter problems in Microsoft Office and use his expertise and legal-level training to find solutions.

Outside of the Microsoft world, Tony is a keen dog owner and lover, football fan, astrophotographer, gardener, and golfer.

Here is a fact-based summary of the story contents:

Try something different:

Data validation in Excel lets you restrict what can be entered into a cell, whether whole numbers, decimals, dates, or a certain number of characters. However, one of the most powerful capabilities of this tool is custom data validation, which lets you create a more specific rule using a formula.

In this guide, I'll show you how to create a data validation rule for cells that require strict combinations of letters and numbers in a particular order.

To follow along as you read this guide, download afree copy of the Excel workbook used in the examples. After you click the link, you'll find the download button in the top-right corner of your screen.

Pro Tip: Don't Construct Your Formula in the Data Validation Dialog Box

If you've useddata validation in Excel before, you probably selected the relevant cells, clicked "Data Validation" in the Data tab on the ribbon, and created the rule in the Data Validation dialog box by selecting "Custom" and typing the formula in the Formula field.

The Data Validation dialog box in Excel is opened, and Custom is selected in the Allow field.

However, when creating more complex data validation formulas, do this in the spreadsheet itself, and then copy the result into the Formula field of the Data Validation dialog box once you're done.

There are two reasons why this is the best approach. First, when typing a formula into a cell, you get tooltips to help you with the arguments—something you don't get when typing a formula in a dialog box in Excel. Second, you can test each part of the formula one by one to check that it works as expected.

Example 1: One Letter and Six Numbers

Suppose you have this Excel worksheet, with people's names in column A, company names in column B, and company codes in column C.

An Excel spreadsheet, with people's names in column A and company names in column B, with column C, blank, ready to accept company codes.

Your aim is to create a data validation rule so that the correct combination of letters and numbers is input into each cell in column C. Specifically, each company code should comprise one letter, followed by six numbers.

Step 1: Define the Rules

The first step is to define the individual rules you'll use to construct your formula. In this case, the company code needs to contain seven characters in total, the last six characters must be numbers, and the first character must be text.

An Excel spreadsheet containing data on the left and an area on the right where data validation sub-rules will be created.

Step 2: Construct the Formulas

Now, you're ready to go ahead and construct a formula for each rule you set out in Step 1. At this point, type a valid dummy code into cell C2, so you can check that the formulas you're about to type work as expected.

An Excel spreadsheet, with names in column A, company names in column B, and a single company code typed into cell C2.

For the rule that checks the number of characters, you need to use theLEN function. So, in cell F2, type:

=LEN(

and select the first cell to which you want to apply the rule (in this case, cell C2). Then, close theparentheses, but don't press Enter yet:

=LEN(C2)

=LEN(C2) is typed into cell F2 in Microsoft Excel.

When creating a custom data validation rule, the formula must return theBoolean values TRUE or FALSE. So, now, type=7 at the end of the formula, and press Ctrl+Enter to commit the formula but keep the cell active:

=LEN(C2)=7

This returns TRUE, as the number of characters in cell C2 equals seven.

A LEN formula combined with Boolean logic in Excel returns TRUE, since the referenced cell contains seven characters.

Next, you need to create a rule that tests whether the last six characters are numbers. To do this, in cell F3, you need to use the RIGHT function:

=RIGHT(C2,6)

Credit: Lucas Gouveia/How-To GeekRelated

13 Essential Excel Functions for Data Entry

Automate some of your data entry tasks with these helpful functions.

Posts

If you press Enter at this point, even though the formula correctly returns the last six characters, the result is currently stored as text. So, to convert it to a number, wrap the formula inside the VALUE function:

=VALUE(RIGHT(C2,6))

Now, when you press Ctrl+Enter, the result returns 123456 as a number. You know it's numeric because numbers align to the right by default in unformatted cells.

The VALUE and RIGHT function in Excel are used to return the six rightmost characters in cell C2.

Remember that data validation formulas must return TRUE or FALSE, so the final step for this rule is to wrap the formula inside ISNUMBER:

=ISNUMBER(VALUE(RIGHT(C2,6)))

The ISNUMBER, VALUE, and RIGHT function in Excel are used to verify that the six rightmost characters in cell C2 are numeric.

The final rule needs to check whether the first character is text. You don't want to use the ISTEXT function here, as it will return TRUE for numbers when they're combined with letters in the code. So, instead, you want to create a formula that checks whether the first character isnot a number.

In cell F4, start by applying the LEFT function to the first character in cell C2:

=LEFT(C2,1)

As with the first formula above, you need to force Excel to recognize a number in the code, so wrap the formula inside VALUE:

=VALUE(LEFT(C2,1))

Next, wrap the formula inside ISNUMBER:

=ISNUMBER(VALUE(LEFT(C2,1)))

If you press Ctrl+Enter at this point, the formula returns FALSE.

The ISNUMBER, VALUE, and RIGHT function in Excel return FALSE, since the referenced cell, C2, starts with a letter.

However, you want this to return TRUE if it's text, so wrap the whole formula inside NOT to flip the result:

=NOT(ISNUMBER(VALUE(LEFT(C2,1))))

and press Ctrl+Enter.

The NOT, ISNUMBER, VALUE, and LEFT functions used together in an Excel formula return TRUE, since the first character in the referenced cell is a letter.

The 3 Best Logical Functions I Always Use in Excel

Make Excel evaluate your data.

Posts2

By Tony Phillips

Oct 7, 2024

Take a moment to test the rules. If you add another number to the end of the code, the first formula should return FALSE, as there are now eight characters in the code.

An Excel spreadsheet showing FALSE in cell F2, since the referenced cell is evaluated as having eight characters.

If you turn the last number into a letter, the second formula should return FALSE.

An Excel spreadsheet showing FALSE in cell F3, since the last six characters in the referenced cell are not numbers.

Finally, if you turn the letter at the start into a number, the third formula should return FALSE.

An Excel spreadsheet showing FALSE in cell F4, since the first character in the referenced cell is not a letter.

Step 3: Combine the Formulas

The next step involves combining the formulas into one using the AND function. In other words, you want the final formula to return TRUE only if the three separate parts all return TRUE.

The best way to do this is to add the three formulas to your clipboard. First, head to the "Home" tab on the ribbon, and click the icon in the bottom-right corner of the Clipboard group to launch the Clipboard pane.

The Clipboard pane is opened in an Excel workbook.

If any items are already saved to the clipboard, provided you no longer need them, click "Clear All" to make the following steps easier to follow.

Now, head to the cell containing the first formula, and select and copy everything in the formula bar, except for the equal sign (=). As you do this, you'll see it added to the Clipboard pane.

Part of a LEN formula in Excel is added to the Clipboard pane.

Press Esc to exit the formula bar, and repeat this process for the other two formulas.

The Clipboard pane in Excel contains three formulas, absent equal signs.

Now, in a new cell, type:

=AND(

and follow these steps:

Click the first formula in the Clipboard pane.
Type a comma.
Click the second formula in the Clipboard pane.
Type a comma.
Click the third formula in the Clipboard pane.
Type a closing parenthesis, and press Ctrl+Enter.

A formula in Excel evaluates whether a value in cell C2 contains seven characters, ending with six numbers and starting with one letter.

This formula now combines all three sub-formulas into one, meaning all three conditions must return TRUE for the combined formula to also return TRUE.

Step 4: Enter the Combined Formula Into the Data Validation Dialog Box

Now that the rule is fully constructed, you can apply it to the relevant cells. First, head to the cell containing the formula, select and copy the formula in the formula bar.

A formula in Excel's formula bar is selected and copied.

Next, press Esc to exit the formula bar, select cells C2 to C6, and click "Data Validation" in the Data tab on the ribbon.

Then, select "Custom" in the Allow field, activate the Formula field, and press Ctrl+V to paste the formula.

A formula is pasted into the Formula field of Excel's Data Validation dialog box.

Insert an input message and error alert in the other tabs of the Data Validation dialog box to remind people of the rules for these cells and trigger a helpful message if someone enters an invalid value.

Now, check that the data validation rule works by entering some valid and invalid codes into the cells to which the rule is applied.

An Excel data validation error alert informing the user that the cell must contain a code with one letter followed by six numbers.

Finally, clear the helper cells you used to create the custom data validation formula.

To duplicate the same data validation rules in other cells, copy one of the cells already containing the rule, select the destination cell or cells, press Ctrl+Alt+V to launch the Paste Special dialog box, check "Validation," and click "OK."

Example 2: Two Specific Letters and Four Numbers

This time, the codes in column C must contain the letters "CC," followed by four numbers. To create this rule, follow the same steps as in Example 1, but make some minor tweaks to the formulas. Since the process is very similar, I'll run through the steps much more quickly, but you can always head back to Example 1 to remind yourself of how it works.

If you're following along, you'll need to clear the previous rule you created in Example 1 before starting Example 2. To do this, select cells C2 to C6, open the "Data Validation" dialog box, and click "Clear All." Then, do the same when you move from Example 2 to Example 3.

Step 1: Define the Rules

For this custom data validation formula, the code must contain six characters, the last four of which must be numbers, and the first two must be the letters "CC." So, create an area in your spreadsheet where you'll create individual formulas for these rules, and type a valid dummy code into cell C2.

An Excel spreadsheet containing data on the left and cells on the right where data validation sub-rules will be created.

Step 2: Construct the Formulas

Here's the formula to evaluate whether the code in cell C2 contains six characters:

=LEN(C2)=6

The formula to check whether the last four characters are numbers goes as follows:

=ISNUMBER(VALUE(RIGHT(C2,4)))

Finally, to verify that the first two letters are "CC," type:

=LEFT(C2,2)="CC"

Always usedouble quotes around text in Excel formulas.

All three formulas should return TRUE.

Three Boolean formulas in Excel evaluate to TRUE, since cell C3 matches all their criteria.

Take a moment to test the formulas by entering valid and invalid entries into cell C2.

Step 3: Combine the Formulas

Now, use the Clipboard pane to insert the three formulas as separate arguments for the AND function, remembering to separate them with commas and add a closing parenthesis at the end.

Parts of formulas are pasted into an AND formula in Microsoft Excel via the clipboard.

Then, press Ctrl+Enter to confirm that the formula returns TRUE.

A formula in cell F5 in Excel returns TRUE because all three conditions are met.

The Microsoft Office Clipboard Can Do More Than You Think

Copy more than one item simultaneously.

Posts2

By Tony Phillips

Feb 17, 2025

Step 4: Enter the Combined Formula Into the Data Validation Dialog Box

Finally, copy the combined formula via the formula bar, select the cells where you want to apply the custom data validation rule, and paste the copied formula into the Formula field of the Data Validation dialog box.

The Data Validation dialog box in Excel, with Custom selected in the Allow field, and a formula inserted into the Formula field.

Set the input message and error alert, and click "OK." And that's it! The rules are created, and entries into the selected cells are restricted, meaning you can clear the helper cells.

Example 3: Two Letters and Three Numbers

In this final example, your aim is to create a data validation rule that requires the cells to contain two letters and three numbers. Although the process is similar, you must take care when defining the rule for the two letters at the start of the code.

Step 1: Define the Rules

When creating formulas for custom data validation rules, always break down the individual parameters in your spreadsheet. In this example, the code must contain five characters overall, with the last three being numbers and the first two being letters. Also, type a valid dummy entry into cell C2 to check your formulas work as expected.

An Excel spreadsheet containing data on the left and cells on the right where nested data validation formulas will be created.

Step 2: Construct the Formulas

The rule for the character count is always the same, as it doesn't need to distinguish between letters and numbers. The only thing you have to tweak is the value in the formula—this time, it's five:

=LEN(C2)=5

Next, generate the formula to check that the last three characters are numbers:

=ISNUMBER(VALUE(RIGHT(C2,3)))

Finally, you need to verify that the first two characters are letters. At this point, you might try using the same formula as in Example 1, but change the LEFT count to 2:

=NOT(ISNUMBER(VALUE(LEFT(C2,2))))

However, if you type one letter and four numbers into cell C2, the formula returns TRUE, even though the intended conditions aren't met.

An Excel formula in cell F4 returns TRUE, even though the referenced cell does not appear to meet the conditions.

This is because cells containing both letters and numbers are considered textual, meaning the string A1 is evaluated as two letters. So, to fix this, you need to construct separate formulas to check that each of the first two characters is a letter.

The formula in cell F4 for the first letter is the same as what you just tried (but didn't work), except for the LEFT count, which needs to be 1:

=NOT(ISNUMBER(VALUE(LEFT(C2,1))))

A logical formula in Excel returns TRUE because the first character in the referenced cell is not a number.

The formula in cell F5 for the second character requires the MID function. So, begin a MID formula, and reference cell C2:

=MID(C2,

The next argument is the start number. In this case, you want to start at the second character:

=MID(C2,2

The final argument of the MID function specifies the number of characters. In this example, it's one character:

=MID(C2,2,1)

When you press Ctrl+Enter, Excel correctly returns 1, as this is currently the second character in the company code.

The MID function in Excel returns 1, because the second character in the referenced cell is 1.

Now, you want to use the same three functions you used to determine whether the first character is a letter, so head back to cell F4, and select and copy the first three functions in the formula bar, including the opening parenthesis for each.

The first three functions in a nested Microsoft Excel formula are selected and copied.

Next, press Esc, and head back to cell F5, which contains the MID formula. Then, in the formula bar, paste the copied functions after the equal sign, before adding three closing parentheses:

=NOT(ISNUMBER(VALUE(MID(C2,2,1))))

Three copied functions are pasted into the formula bar before an existing MID function.

When you press Enter, the formula correctly identifies that the second character in cell C2 is currently a number, thus returning FALSE.

The NOT, ISNUMBER, VALUE, and MID functions in Excel returning FALSE, because the second character in the referenced cell is a number.

However, changing the second character to a letter returns TRUE, exactly as you hoped, meaning all the conditions are now met for this code.

A formula in Excel returns TRUE, because the second character in the referenced cell is a letter.

Use the MID function to test for a character type anywhere within a value in Excel. Simply change the function's second and third arguments to identify the correct starting point and number of characters.

Make some tweaks to the code in the referenced cell to check the formulas work, and then move on to Step 3.

Step 3: Combine the Formulas

As in Examples 1 and 2, using the Clipboard pane, copy and paste the formulas (but not the equal signs) as separate arguments for the AND function in cell F6, remembering to separate them with commas. Then, press Ctrl+Enter to confirm that the formula returns TRUE.

Four separate arguments are used for the AND function in Excel to determine whether the value in cell C2 meets various criteria.

Step 4: Enter the Combined Formula Into the Data Validation Dialog Box

Finally, copy the long formula from the formula bar, select the cells to which the custom data validation rule should be applied, and paste the formula into the Formula field of the Data Validation dialog box.

A long formula is pasted into the Formula field of the Data Validation dialog box.

Remember to use the other two tabs in the Data Validation dialog box to set an input message and error alert, and click "OK" when you're done.

Excel's data validation tool is best known for allowing you toadd a drop-down list to a cell, making data entry easier and more consistent. Similarly, you cancreate drop-down lists from columns of data in a formatted Excel table, meaning if you add more rows, the data validation source automatically adjusts to include the extra cell or cells.