Exceljet

Quick, clean, and to the point

Reverse text string

Excel formula: Reverse text string
Generic formula 
=TEXTJOIN("",1,MID(A1,{10,9,8,7,6,5,4,3,2,1},1))
Explanation 

You can reverse a text string with the TEXTJOIN and MID functions, by using an array constant. In the example shown, the formula in C5 is:

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

How this formula works

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 hardcoded 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

Due sometime in 2019 via Office 365 subscription, the the SEQUENCE function is made to generate dynamic number arrays. With SEQUENCE, a dynamic solution looks like this:

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

 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables