The Problem
We have a list of alphanumeric codes.
Each code consists of a single letter (A, B, C, etc.)
followed by a 3-digit number.
These codes should appear in alphabetical order, but sometimes they are out of sequence.
We want to flag out-of-sequence codes.
is out of sequence?
Download the worksheet below and take the challenge!
Hint -This videoshows some tips for how to solve a problem like this.
Assumptions
Here are some working solutions.
It’s important to understand that there are many, many, ways to solve common problems in Excel.
The answers below are just my personal preference.
In all of the formulas below, function names are clickable if you want more information.
I originally went with this formula:
Note MID returns text.
By adding 1 and adding zero, we get Excel to coerce the text into a number.
The multiplication inside the logical test inside IF usesboolean logicto avoid another nested IF.
I’m not sure why I didn’t use RIGHT, which would work fine here as well.
Also note LEFT doesn’t require the number of characters and will return the first character if not provided.
If the first character is the same, we are checking numbers as above.
If not, we are checking the first letter only.
