Challenge #1
What formula in the “Check” column will place an “x” next to a code that is out of sequence? In this challenge, we are only checking that the numeric portion of the code is out of sequence, not that the letter itself is out of sequence.
Challenge #2
How can the formula above be extended to check if “alpha” part of the code (A,B,C, etc.) is out of sequence? For example, we should flag a code that begins with “A” if it appears after a code that begins with “C” or “B”.
Download the worksheet below and take the challenge! Note: there are 2 sheets in the workbook, one for Challenge #1, one for Challenge #2. Hint - This video shows some tips for how to solve a problem like this.
Assumptions
Challenge #1
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 uses boolean logic to 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. Based on some of the clever responses below, we can optimize a bit more: Here, the math operation of subtracting MID from MID automatically coerces the text values to numbers.
Challenge #2
For this solution, I used several nested IFs (line breaks added for readability): I did this because the first test LEFT(B5)=LEFT(B6) determines whether we are checking numbers or letters. If the first character is the same, we are checking numbers as above. If not, we are checking the first letter only. Note the CODE function will return the ascii number of the first character if a text string contains more than 1 character. This feels like a hack, and it makes the code less understandable perhaps, but it works :) If that offends your sensibilities, use LEFT as above inside CODE to deliver just the first character. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
