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

# 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.

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!):

`=JOIN("",ARRAYFORMULA(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)))`

## Can I see an example worksheet?

## 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:

**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:

**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)))`

**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: