Excel random value from a string containing substrings-CodePudding

I have this table that is defined as "Table1".

I also have a string which contains substrings inside it with delimiter ", " (comma space)

I would like to get a formula to get one of those substrings randomly.

Excel version: Excel 365

Thanks in advance.

CodePudding user response：

We can use FILTERXML to split the string and INDEX with RANDBETWEEN to randomly select one of the items:

=LET(valarr,FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s"),INDEX(valarr,RANDBETWEEN(1,COUNTA(valarr))))

You can change the A2 to [@string containing substring] for the structured reference.

CodePudding user response：

You could use MID and SEQUENCE to find the words:

=LET(
    Txt, "Apple, Orange, Banana, Peach",
    Delim, ",",

    Seq, SEQUENCE(LEN(Txt)),
    TxtArr, MID(Txt, Seq, 1),
    StartArr, FILTER(Seq, (TxtArr = ",")   (Seq = 1)),
    EndArr, FILTER(Seq, (TxtArr = ",")   (Seq = LEN(Txt))),
    LenArr, EndArr - StartArr   1,
    WordArr, TRIM(SUBSTITUTE(MID(Txt, StartArr, LenArr), Delim, "")),
    INDEX(WordArr, RANDBETWEEN(1, COUNTA(WordArr)))
)

This creates an array of all string characters called TxtArr, then uses Filter to find the delimiters. StartArr (also) includes the first index and EndArr also includes the last index. LenArr is the approximate length of each word. Using TRIM and SUBSTITUTE removes the delimiter and any extra spaces.