Formula Errors

How to Validate a Two-Letter, Six-Digit Serial Number in Excel

Jason MillerJason Miller Nov 11, 2026 869 views

Question details

The user needs to validate alphanumeric serial numbers in Excel to ensure they strictly follow the format of two letters followed by six digits (LLNNNNNN).

How to Validate a Two-Letter, Six-Digit Serial Number in Excel
Product
Excel 2016
Device & OS
not provided
Scenario
Performing data entry, data cleaning, or auditing where product codes or serial numbers must meet strict 8-character formatting rules.
Observed behavior
Requires a formula setup that returns TRUE for correctly formatted entries and FALSE for incorrect ones, enabling conditional formatting to flag invalid data.
Before you start

Ensure your serial numbers do not contain hidden leading or trailing spaces, as these invisible characters will alter the string length and cause the validation formula to fail.

Solution 1Recommended

Use a Combination of Logical and Text Functions

Apply a combined formula using AND, LEN, ISERR, and ISNUMBER to verify the exact string length, confirm the first two characters are letters, and check that the last six are numbers.

This validation relies on evaluating different segments of the text string. The LEN function ensures the total character count is exactly eight. The LEFT and MID functions extract the first and second characters, checking if converting them to numbers results in an error (which means they are text). Finally, the RIGHT function confirms the last six characters are numeric.

1
Select a helper column

Click on the cell immediately to the right of your first serial number (for example, cell C2 if your serial number is in B2).

2
Enter the validation formula

Type the following formula exactly: =AND(LEN(B2)=8,ISERR(--LEFT(B2)),ISERR(--MID(B2,2,1)),ISNUMBER(--RIGHT(B2,6)))

3
Apply formula to the rest of the column

Press Enter to see the result. Then, click and drag the fill handle (the small square at the bottom right of the cell) down to copy the formula for all serial numbers.

Use a Combination of Logical and Text Functions
Understanding the Double Dash (--): The double dash (double unary operator) in the formula forces Excel to convert text digits into actual numbers. If the text is a letter, the conversion throws an error, which triggers the ISERR function to return TRUE.
Smart Data Validation Tools

Use WPS Spreadsheet to Validate Complex Data Fast

WPS Spreadsheet offers full support for advanced logical functions and conditional formatting. Whether you need to validate serial numbers, perform bulk data cleaning, or highlight errors, WPS Spreadsheet handles complex formulas exactly like Excel.

  1. 1. Open your data file: Launch WPS Spreadsheet and open the document containing your serial numbers.
  2. 2. Apply the validation formula: Click an empty cell next to your data and paste the combined AND validation formula.
  3. 3. Set up visual formatting: Navigate to the Home tab and use the Conditional Formatting tool to instantly flag mismatched data entries.
  4. 4. Save in Excel format: Save your work seamlessly as an .xlsx file to ensure compatibility with other users.
100% compatibility with all Microsoft Excel formulas, including array and text functions.Built-in advanced Data Validation and Conditional Formatting tools.Free and lightweight alternative, ensuring smooth performance even with large datasets.
microsoft office alternative - wps office

Frequently Asked Questions

Can I prevent users from typing invalid serial numbers altogether?

Yes. You can restrict data entry using Data Validation. Select the cells, go to the Data tab, click Data Validation, choose 'Custom' under the Allow dropdown, and paste the validation formula. This will display an error alert if someone types an invalid serial number.

What if my serial number has three letters and five digits?

You can easily adjust the formula. Change the MID function to extract the third character, e.g., ISERR(--MID(B2,3,1)), and adjust the RIGHT function to grab the last 5 characters: ISNUMBER(--RIGHT(B2,5)).

Why does the formula return FALSE even when the serial number looks correct?

This usually happens if there are invisible spaces before or after the serial number. The LEN(B2)=8 condition will fail if a trailing space makes the length 9 characters. Use the TRIM function to clean your data first.