Excel random value from a string containing substrings

Question

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.

score 3 · Accepted Answer · answered Oct 28 '21 at 16:21

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.

score 2 · Answer 2 · answered Oct 28 '21 at 20:32

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.

Excel random value from a string containing substrings

2 Answers2