Tag: Google Sheets

In this example, you’ll see how to reverse text in Google Sheets.

To start, enter some text into cell A1 and the formula in cell B1, to reverse the order of the text:

Reverse Text In Google Sheets

What’s the formula?

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

It might be useful if you wanted to find the last character in a string, or the last occurrence of a character using the FIND() function.

Here’s an alternative, which reverses the capitalization too (submitted by Michael D over email – thanks!):

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:

Use the LEN function to calculate the length of the text string and turn it into a range string with “1:”&LEN(A1)

Use the INDIRECT function to turn this string range reference into a valid range reference.

Finally wrap with ROW to convert into a row number list.

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:

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

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:

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:

so your output is now:

Step 5:

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

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:

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:

Final output:

Here’s the formula in action again:

Further Reading

You might also like this article on Using Text Rotation to Create Custom Table Headers in Google Sheets.