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!

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!

• excel
• range
• sequence

Share Improve this question Follow asked Mar 8 at 0:45 DingoDingo 13311 gold badge11 silver badge1414 bronze badges 4

• 1 "The above range can hold any combination of numbers in between" - meaning what? If it's a list, just use MATCH or LOOKUP – 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 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 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 Commented Mar 9 at 2:29

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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

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