How to Validate a Two-Letter, Six-Digit Serial Number in Excel
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).

- 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.
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.
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.
Click on the cell immediately to the right of your first serial number (for example, cell C2 if your serial number is in B2).
Type the following formula exactly: =AND(LEN(B2)=8,ISERR(--LEFT(B2)),ISERR(--MID(B2,2,1)),ISNUMBER(--RIGHT(B2,6)))
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.

Highlight Invalid Entries with Conditional Formatting
Use the NOT function wrapped around the validation formula to automatically highlight incorrectly formatted serial numbers directly in your data column.
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. Open your data file: Launch WPS Spreadsheet and open the document containing your serial numbers.
- 2. Apply the validation formula: Click an empty cell next to your data and paste the combined AND validation formula.
- 3. Set up visual formatting: Navigate to the Home tab and use the Conditional Formatting tool to instantly flag mismatched data entries.
- 4. Save in Excel format: Save your work seamlessly as an .xlsx file to ensure compatibility with other users.

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.




