Excel formula to determine missing numbers

This is actually pretty easy to do with a few helper columns in Excel. First, split your case numbers into two parts, one column for the year (using =LEFT(S2,4)) and another for the sequence number (using =VALUE(RIGHT(S2,3))). Then sort your data by year and sequence number. Once sorted, you can add a formula like =IF(AND(A2=A1,B2<>B1+1),"Missing "&TEXT(B1+1,"000"),"") to quickly flag any gaps within the same year. This will show messages such as “Missing 002” when a number in the sequence is skipped. You don’t need to reinstall Excel, it’s just about setting up the right formulas to check for missing numbers automatically.

------------------------------------
Don't forget to mark as solution if my answer suits you