Check if a sequential value in a range

Question

I am stuck in a unique situation. I have a range:

ABCD0001 to ZZZZ9999

The above range can hold any combination of numbers in between. The individual values reset at Z (next becomes A) or 9 (next becomes 0)

If a user enters any sequence such as AZCD2192, I want to be able to check if it exists in the above range or not. This is bit complicated for me as it requires creating VBA(?) Can this be done via Excel formulas? Any lead is appreciated!

"The above range can hold any combination of numbers in between" - meaning what? If it's a list, just use MATCH or LOOKUP — Michal
– Michal, Commented Mar 8 at 1:17
I'm a bit confused by this - what happens after ABCD9999, would it go to ABCE0001 or maybe ABCE0000 ? I'm reading it as the first four digits being in base 26 and the last four in base 10? Also why isn't AAAA0001 valid or AAAA0000? — Tom Sharpe
– Tom Sharpe, Commented Mar 8 at 12:57
@TomSharpe I have assumed that ABCD9999 => ABCE0000. I have also assumed that AAAA0000 is less than ABCD0001. — Ron Rosenfeld
– Ron Rosenfeld, Commented Mar 8 at 18:00
After ABCD9999 is ABCE0001. "AAAA0001" isn't valid because its 'used up' in the scenario I am facing and user would not enter it. This is also not a list - I am only given 1st and last values of the sequence — Dingo
– Dingo, Commented Mar 9 at 2:29

Ron Rosenfeld · Accepted Answer · 2025-03-09 00:05:57Z

4

Edited for a shorter and possibly more efficient algorithm

Start = the first number in the sequence
End = the last number in the sequence
User Input = the obvious
All the entries are valid with four letters and four numbers.

=LET(
    s, Start,
    e, End,
    u, User_Input,
    a, SORT(VSTACK(s, e, u)),
    b, XMATCH(u, a),
    OR(b = 2, s = u, e = u)
)

Sort the three entries
if user input is in the range, it will either sort in the middle, or be equal to the start or end values.
A validity test could be added to the formula, or you could set data validation on the input cells

edited Mar 9 at 0:05

answered Mar 8 at 1:49

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Sharpe · Accepted Answer · 2025-03-09 14:00:13Z

2

There are different interpretations of the question, but it seemed to me that you could just compare the first and last four characters of the string like this:

=LET(s,A1, min,"ABCD0001", max,"ZZZZ9999", AND(LEN(s)=8,LEFT(s,4)>=LEFT(min,4),LEFT(s,4)<=LEFT(max,4),RIGHT(s,4)>=RIGHT(min,4),RIGHT(s,4)<=RIGHT(max,4)))

Within those two groups the sequence will just increase alphabetically or numerically.

Here are some examples:

String			Test
ABCD0001			TRUE
ABCD9999			TRUE
ZZZZ9999			TRUE
XYZ12345			FALSE
ABCDE789			FALSE
AAAA0000			FALSE

edited Mar 9 at 14:00

answered Mar 8 at 21:42

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Check if a sequential value in a range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related