How to Validate an ID Format with a Formula in Excel

Check that IDs follow a pattern like EMP-1234 using LEN, LEFT, RIGHT and ISNUMBER inside AND.

You will learn to

• Check an ID’s length
• Check its prefix as well
• Confirm the last characters are digits

How it works

To validate a code such as EMP-1234, break the pattern into checks and combine them with AND: the length is 8 (LEN(A2)=8), it starts with EMP- (LEFT(A2,4)="EMP-") and the last four characters are digits (ISNUMBER(--RIGHT(A2,4))). The double minus converts "1234" to a number and errors on "12O4", which ISNUMBER turns into FALSE.

Two gotchas: the = comparison ignores case, so "emp-" passes the prefix test (use EXACT if case matters), and the same rules work in Data Validation with a custom formula to stop bad IDs being typed in the first place.

=AND(LEN(A2)=8,LEFT(A2,4)="EMP-",ISNUMBER(--RIGHT(A2,4)))