I am currently trying to extract the prefix of a store ID to be able to then generate a list of stores with only that prefix.
Cell D1 has that formula to extract the unique prefix :
=TRANSPOSE(UNIQUE(LEFT(C2#, MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2#&"0123456789"))-1)))
Cell C2 has that formula to extract the unique store ids from another sheet :
=UNIQUE(INDEX('Male Shoes'!A1#,,6))
The problem is that the formula in D1 only returns the first two characters from all the unique prefixes instead of using the correct value for each prefixes.
I have setup in column I the same formula as in D1 without the TRANSPOSE() and UNIQUE() functions and remove the # to see if that would return the correct value. I dragged it down the length of the C column.
=LEFT(C2, MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2&"0123456789"))-1)
In Cell J2 I put the same formula has I2 but kept the # as a control.
=LEFT(C2#, MIN(FIND({0,1,2,3,4,5,6,7,8,9},C2#&"0123456789"))-1)
I believe the MIN() function is returning the minimum for the entire array and not for each row. I haven't found how to mitigated that problem anywhere online.
In my sample data that is not a problem since all the columns in D through G gave me the lists I was expecting but as more countries get added I might end up with duplicate country prefix. (i.e.: If the prefixes get shortened to 2 characters - Germany=GE and Georgia=GE)
If it is always 2 or three a simple
IF
instead of FIND will work:Edit:
If there is only one time that it switches from alpha to numeric we can use:
This does not care how many characters are in the string, only that there is only 1 time that it switches from alpha to numeric. So
ABDEFGHTEV4567
will work butA3D4
will not.