VideoPhoto

Showing posts with label reverse. Show all posts
Showing posts with label reverse. Show all posts

20 October, 2021

In Reverse: Going Backwards with ARRAY formulas

Do you need to type or use expressions or numbers in reverse?

Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:

=SUM(VALUE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*10^(ROW(INDIRECT("1:"&LEN(B2)))-1)) 

However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.

So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:

The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.