My below formula that you can use to reverse text and numbers in Google Sheets works like this. The formula first split the characters in the text or digits in the number and sort them in descending order and join them. So the text or number in question got reversed. It’s a single piece of formula as below.
Google Sheets Formula That Can Reverse Text / Numbers / Sentence / Special Characters in a Cell:
=ArrayFormula(iferror(JOIN("",mid(A1,SORT(row($A$1:indirect("A"&len(A1))),1,FALSE),1))))
In this formula, you may want to change the cell address A1, not the $A$1, with the cell reference that you want. You can see this reference twice in the formula. Change both that.
In my case, the text that I want to reverse is in cell A1. So I kept the same in the formula.
Example: Text and Numbers Reversed in Column C.
Explanation to the Formula to Reverse Text and Numbers in Google Sheets
I’ve already explained the logic. To reverse a text using a formula in Google Sheets, the best solution is to split the text into individual characters.
It’s applicable to numbers and dates too. For this, I am not going to use the SPLIT function. Instead, the best function for this is MID in Google Sheets.
You can use the MID function to extract each character one by one. Let’s see how?
The Formula to Split String / Text / Number / Sentence into Individual Characters
For this example, the text in Cell A1 is “Info Inspired”. Now see one formula.
=mid(A1,1,1)
This MID formula will extract the first character in cell A1, i.e. “I”. We want to extract all the characters. That means virtually we want to split the text to individual characters in an Array.
To do that we should feed the MID function arguments automatically. I’ll come to that. Before that see one more MID formula.
=mid(A1,13,1)
This MID formula is to extract the last letter in the string “Info inspired” in cell A1.
Here is the MID function syntax.
MID(string, starting_at, extract_length)
In this, the argument STRING is the cell reference A1 that containing the string “Info Inspired”.
The argument EXTRACT_LENGTH indicates the number of characters that we want to extract.
We can put here 1. What we should do is to feed the STARTING_AT argument automatically.
In our string in Cell A1, there are thirteen characters. So I want to feed 13 numbers from 1 to 13 in MID as below.
MID(A1,1,1), MID(A1,2,1), MID(A1,3,1), ....... MID(A1,13,1)
To auto feed cell references in any formula, the best available two functions in Google Sheets are ROW and COLUMN. Here I’ve used the ROW function.
MID Function: How to Auto Feed the staring_string Argument
The following formula does the auto feeding. This formula generates the number 1 to 13.
=ArrayFormula(row($A$1:indirect("A"&len(A1))))
It’s like;
=ArrayFormula(row($A$1:A13))
We are unsure about how many characters are there in the string to split into characters. So I’ve used Google Sheets LEN function to find the length and with the help of INDIRECT, I could refer this as the cell reference. So that I can set the auto feeding from 1 to 13.
See the main formula once again below. The highlighted part just splits the string into individual characters.
=ArrayFormula(iferror(JOIN(“”,mid(A1,SORT(row($A$1:indirect(“A”&len(A1))),1,FALSE),1))))
If I take this formula out form the master formula and apply it in a new cell, the result would be as below.
I want the split string itself in reverse order. So instead of;
MID(A1,1,1)
The formula should extract the number from;
MID(A1,13,1)
That’s in the reverse order. Before feeding the starting_at in MID, I’ve sorted the feed number 1 to 13 in descending order. So it would be like 13 to 1.
Then finally the JOIN function joins the individual characters back to a string. That’s all about reverse text and numbers in Google Sheets.
Conclusion
The above formula to Reverse Text and Numbers in Google Sheets can even reverse dates and special characters. Type a sentence in cell A1 and use the above formula in cell B1. The formula would reverse the sentence too!
Please try it and drop your valuable comments below. Enjoy!
