Excel Split a list of letters into cells to generate all combinations

Question

source cell is this string: A[B,C,D]E need to generete all combination into different cells:

ABE
ACE
ADE

Should work with more complicated source like: A[B,C,D]E[F,G]:

ABEF
ACEG
ADEF
ABEG
ACEF
ADEG

Answer 1

In the event you have access to REDUCE(), you could try:

Formula in B1:

=LET(X,"<t><s>",Y,"</s><s>",Z,"</s></t>",TRANSPOSE(FILTERXML(X&SUBSTITUTE(REDUCE("",FILTERXML(X&SUBSTITUTE(SUBSTITUTE(A1,"]","["),"[",Y)&Z,"//s[node()]"),LAMBDA(a,b,TEXTJOIN(",",,IFERROR(FILTERXML(X&SUBSTITUTE(a,",",Y)&Z,"//s"),"")&TRANSPOSE(FILTERXML(X&SUBSTITUTE(b,",",Y)&Z,"//s"))))),",",Y)&Z,"//s")))

And yes, that's seriously hard to explain in a nutshell, but I'll try it anyway =)

• The 1st parameter of REDUCE() is our starting value, in our case we want string-values so we use an empty string "". Later on in this function we can refer to this starting value as 'a'.
• The 2nd parameter is either a range-reference or an array. In this particular case an array since we used FILTERXML() to chop-up the input into different sections.
• In the 3rd parameter sits a nested LAMBDA(). This function has two custom named variables, 'a' and 'b'. Note that we allready have the 'a' value down in the 1st parameter of REDUCE(). The 'b' value is the value of each of the elements of our array in the 2nd parameter.

The idea here now is that with each pass of the next element the LAMBDA() perform a recursive function. When our first element passes, the essence of the nested function is to concatenate each child-node of the previous element (or empty string in the 1st passing) with each child-node of the current element. REDUCE() will only return the final outcome when all the elements have been passed through LAMBDA().

This final string will then be finally 'split' on itself through FILTERXML(). For a better understanding on this particular function I'd like to refer to this older post.

In the image below I tried to address and visualize this whole process where the input is 'split' into an array which on itself is input for the process to reduce the array. The final outcome is then also 'split'.

---

Answer 2

Edit: My bad, I forgot to combine the characters in different ways. I'll update a fix soon.

---

Original Answer:

Assuming you are only looking at single character strings to combine, you can do this without a lambda function. Please note, I did not optimize this at all:

=LET(
    Txt, A1,
    Seq, SEQUENCE(LEN(Txt)),
    TxtArr, MID(Txt, Seq, 1),
    Open, IFERROR(FIND("[",TxtArr),0),
    Close, IFERROR(FIND("]",TxtArr),0) * -1,
    Starts, FILTER(Open * Seq, Open <> 0),
    Lens, FILTER(-Close * Seq, Close <> 0) - Starts + 1,
    ORs, SUBSTITUTE(MID(Txt, Starts + 1, Lens - 2),",",""),
    OrGroup, MMULT(IF(Seq >= TRANSPOSE(Seq)=TRUE,1,0),Open),
    Mask, -MMULT(IF(Seq >= TRANSPOSE(Seq)=TRUE,1,0),Open+Close) + 1,
    Masked, MID(Txt, Mask*Seq, 1),
    ReplacedMasked, IF(Masked = "]", INDEX(ORs, OrGroup), Masked),
    CleanArray, FILTER(ReplacedMasked,ISERROR(ReplacedMasked)=FALSE),
    ArrayLens, LEN(CleanArray),
    ArrayProd, PRODUCT(ArrayLens),
    ExtendArray, REPT(CleanArray, ArrayProd/ArrayLens),
    CharacterArray, MID(TRANSPOSE(ExtendArray), SEQUENCE(ArrayProd),1),
    ReturnLen, ROWS(CleanArray),
    MID(CONCAT(CharacterArray), SEQUENCE(ROWS(CharacterArray))* ReturnLen - ReturnLen + 1, ReturnLen)
)

This formula takes your text, and finds brackets. It creates an array of character options called CleanArray. It finds the total number of combinations repeats the characters in CleanArray so that each row contains a string the length of all options. It combines the first character of each string, then the second character of each string, and so on. Last, it concatenates everything together and re-separates it into rows.

A	A[B,C]	A[B,C][D,E]
A	AB	ABD
	AC	ACE
		ABD
		ACE