@mrxware
=LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null"))
So if we digest it in parts:
- LET(DATA,A1:A19,...) : allows you to define names/variables where in this case DATA = A1:A19. This way you only need to change the range once for DATA and it updates for all instances of DATA in the formula. The formula you want to execute goes into the "..." space.
- MID(DATA,LEN(DATA)-8,2) : generates a dynamic array of the 8th and 9th character in the range i.e. a column of 2 digits (8th and 9th character from right)
- --MID(DATA,LEN(DATA)-8,2) : normally the use of "--" converts TRUE and FALSE values to 1 and 0 respectively, but in this instance it converts numerical STRING into NUMBER. If your data range is always NUMBER then this can be disregarded.
- --MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58} : Checks if the double digit is equal to 50, 52 ... 58 for each row of your range. This will return an array of size (n x 6) with either TRUE or FALSE values. Rows with one match will have one TRUE in the respective row.
- --(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}) : Converts the TRUE or FALSE values to 1 or 0 respectively. Rows with one match will have one "1" in the respective row.
- SEQUENCE(6) : Generates an array of size 6 x 1 with integers 1 to 6
- MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)) : Matrix multiplies the array of size (n x 6) made up of 1's and 0's with the array of size (6 x 1). The resultant is an array of size (n x 1) whereby rows with one match will have a positive integer in the respective row whilst no matches results in a 0.
- =LET(DATA,A1:A19,
IF(MMULT(--(--MID(DATA,LEN(DATA)-8,2)={50,52,54,55,56,58}),SEQUENCE(6)),
DATA,"Null")) : the curious thing about the IF statement is that the first value must be a TRUE or FALSE value OR alternatively, a number not equal to 0 for TRUE and 0 for FALSE. In step 7, we now have an array of size (n x 1) made up for positive integers and 0's that define our TRUE and FALSE for each row. Plug it into the IF statement and we get DATA if TRUE or Null if FALSE.
This is a mathematical outlook on solving this problem so I highly suggest replicating steps 2 to 8 separately (replace DATA with A1:A19 for steps 2 to 7) to understand what's happening at each step.
Hopefully this helps.
