Using Substitute or Replace to change specified characters to characters in another cell in a specific order Excel

Question

If you will, consider that cell A1 has the following information: A fantastic band was Abba.

Cell 2 should utilise Replace and/or Substitute the six instances of an in cell 3 with the replacements.

What's in Cell 3 is as follows: 123456

So, cell 2 should appear and read: 1bb2 w3s 4 gre5t b6nd.

I have not yet discovered anything that enables me to achieve this. I may display up to "19 of the results" in a single cell. I estimate that I can extract each character from a 60-cell system using left, check them, and then use left on the replacement item to arrange it in sequence, but it sounds like an incredibly laborious process.

Answer 1

Replace Occurrences of a Substring With a Sequence of Characters

• If the setup is always so convenient, you could use:

=LET(d,A1,s,C1,dl,"a",
TEXTJOIN(MID(s,SEQUENCE(,LEN(s)),1),0,TEXTSPLIT(d,dl,,,1)))

... which is short for (less readable than):

=LET(d,A1,s,C1,dl,"a",
   se,MID(s,SEQUENCE(,LEN(s)),1),
   sp,TEXTSPLIT(d,dl,,,1),
TEXTJOIN(se,0,sp))

• If you want to replace each substring ("a") with an integer sequence, see the shortest formula in JvdV's answer.