I have a column containing single characters, and want to concatenate them in reverse order. How can this be accomplished in an Excel formula, without explicitly providing some list? E.g., If my input is [a;b;c;d] stored in cells A1:A4, I want to get the string "dcba" back. The following Excel function returns the string "abcd":

=CONCAT(A1:A4)

I have tried to reverse the range to A4:A1, but Excel auto-corrects that. There must be some way to tell Excel to reverse the iteration direction, but I haven't been able to find anything. The reason I want to code this using the range syntax, is that recently someone inserted a row into the spreadsheet, but that additional row wasn't picked up in the formula (which is currently written as CONCAT(A4,A3,A2,A1)). I want people to be able to insert/delete rows without having to update the formula.

CodePudding user response：

Use INDEX and iterate it backwards:

=CONCAT(INDEX(A1:A4,SEQUENCE(ROWS(A1:A4),,ROWS(A1:A4),-1)))

With LET to limit the number of refernces:

=LET(rng,A1:A4,rw,ROWS(rng),CONCAT(INDEX(rng,SEQUENCE(rw,,rw,-1))))