Reverse text in a cell in Google Sheets

Not sure of the use case for this, but it’s fun (and a good exercise with formulas)!

In this example, I enter some text into cell A1 and a formula in cell B1 reverses the order of the text:

Text reverse in Google Sheets

How to reverse the order of a text string

What’s the formula?

=ArrayFormula(IFERROR(PROPER(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))),""))

It’s a beast! See below for a detailed breakdown of how it works.

Can I see an example worksheet?

Yes, here you go.

How does this formula work?

Basically we make an array of numbers corresponding to how many letters are in the original text string. Then we reverse that, so the array shows the largest number first. Then we extract each letter at that position (so the largest number will extract the last letter, the second largest will extract the second-to-last letter, etc., all the way to the smallest number extracting the first letter). Then we concatenate these individual letters.

Easy! Err…

The only way to really understand this formula is to break it down, starting from the inner functions and building back out.

Assuming we have the text string “Abc” in cell A1, then let’s build the formula up in cell B1, step-by-step:

Step 1:

=ROW(INDIRECT("1:"&LEN(A1)))

which outputs a result of 1 in cell B1.

Step 2:

Turn the formula into an array formula, by hitting Ctrl + Shift + Enter, or Cmd + Shift + Enter (on a Mac), to the formula above. This will add the ArrayFormula wrapper around the formula:

=ArrayFormula(ROW(INDIRECT("1:"&LEN(A1))))

This outputs 1 in cell B1, 2 in cell B2 and 3 in cell B3:

Array to reverse text

Step 3:

Reverse the output, so 3 is in cell B1, 2 in B2 and 1 in B3, by subtracting from the length of the text in A1 and adding 1 to avoid 0:

=ArrayFormula(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))) + 1)

Step 4:

Use the MID formula to now extract the letters at position 3 (“c”), position 2 (“b”) and position 1 (“A”) and display in cells B1:B3:

=ArrayFormula(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))) + 1 , 1))

so your output is now:

MID formula in Google Sheets

Step 5:

Concatenate so that all the individual outputs are combined into a single cell:

=ArrayFormula(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))) + 1 , 1)))

Reverse text in google sheets

Step 6 (optional):

The string is essentially reversed now, so we could stop here.

However, you can use the PROPER function to capitalize the first letters of each word only, for a true reverse effect:

=ArrayFormula(PROPER(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))) + 1 , 1))))

Step 7 (optional):

Last step is to add in an IFERROR function to avoid an unsightly error message if the input cell (A1) is blank:

=ArrayFormula(IFERROR(PROPER(CONCATENATE(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))),""))

Final output:

Here’s the formula in action again:

Reverse text in google sheets