Reverse text string

At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:

MID(B5,{10,9,8,7,6,5,4,3,2,1},1)

The text argument comes B5, and 1 is specified for the number of characters to extract.

With the string "ABCD" in B5, the output from MID is an array that looks like this:

{"","","","","","","D","C","B","A"}

This array is fed into the TEXTJOIN function as the text1 argument, with delimiter set to an empty string (""), and ignore blank set to TRUE (entered as 1):

=TEXTJOIN("",1,{"","","","","","","D","C","B","A"})

The TEXTJOIN function concatenates each element in the array together, ignoring blanks, and returns the final result, "DCBA"

Dynamic array

The array constant in the above example will only support string up to 10 characters. To use a dynamic array that scales to the right size, you can use a more complicated formula like this

=TEXTJOIN("",1,MID(B5,ABS(ROW(INDIRECT("1:"&LEN(B5)))-(LEN(B5)+1)),1))

More information about generating an array of numbers here.

Dynamic array with SEQUENCE function

Excel 365 supports dynamic array formulas. In Excel 365, the SEQUENCE function can generate dynamic number arrays in one step. With SEQUENCE, the formula above can be simplified to:

=TEXTJOIN("",1,MID(B5,SEQUENCE(LEN(B5),,LEN(B5),-1),1))

Inside SEQUENCE, the LEN function returns the count of characters in B5, 4. This result is used both for the rows argument and the start argument, with -1 provided for the step argument:

SEQUENCE(4,,4,-1) // returns {4;3;2;1}

and this array is delivered to the MID function as the start_num argument.