In this tutorial, I am going to share with you 6 quick tricks to reverse a number in excel. You can easily apply these methods whether the numbers are small or comparatively larger. To achieve this task, we will also see some useful functions that might come in handy in many other excel related tasks. Also, in the last section, I will show you how to reverse the sign of a number.
How to Reverse a Number in Excel: 6 Quick Tricks
To ensure that you can follow along with this excel tutorial easily, I have used only 4-digit numbers. But, you can take as many numbers as you want or you may need. These methods should work similarly in other cases as well.
1. Combining SUM and VALUE Functions
In this first method, we will be combining the SUM function and the VALUE function in Excel to reverse a number. Let us see how to do that.
Steps:
- First, go to cell C5 and enter the below formula:
=RIGHT(SUM(VALUE(MID(B5&"1",ROW(INDIRECT("1:"&LEN(B5&"1"))),1))*10^(ROW(INDIRECT("1:"&LEN(B5&"1")))-1)),LEN(B5))
- Then, press Enter and copy the formula to the cells below using the Fill handle.
- Finally, you should see the reversed numbers in corresponding cells.
🔎 How Does the Formula Work?
- LEN(B5): This counts the number of characters and returns 4.
- LEN(B5&”1″): This adds a 1 to the number and returns 5.
- INDIRECT(“1:”&LEN(B5&”1”)): This references rows 1 to 5.
- ROW(INDIRECT(“1:”&LEN(B5&”1”))): Gives back the referenced row numbers.
- 10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1): Generates the numbers 1, 10, 100, 1000 and 10000 in a column.
- MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1): Returns the numbers 1, 2, 3, 4 and 1 in a column.
- VALUE(MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1))*10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1): Returns 1, 20, 300, 4000 and 10000 in a column.
- SUM(VALUE(MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1))*10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1)): Sums all the values in the column and returns 14321.
- RIGHT(SUM(VALUE(MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1))*10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1)),LEN(B5)): Returns 4321 from the sum.
Read More: How to Paste in Reverse Order in Excel
2. Using SUMPRODUCT Function
The SUMPRODUCT function, as the name suggests, multiplies ranges together and returns their sum. We can use this feature to reverse a number in excel.
Steps:
- To begin with, double-click on cell C5 and type in the formula below:
=RIGHT(SUMPRODUCT(VALUE(MID(B5&"1",ROW(INDIRECT("1:"&LEN(B5&"1"))),1))*10^(ROW(INDIRECT("1:"&LEN(B5&"1")))-1)),LEN(B5))
- Now, if you press Enter and copy this to the cells below, all the numbers should be in reverse form.
🔎 How Does the Formula Work?
- SUMPRODUCT(VALUE(MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1))*10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1)): Similar to the SUM function, adds all the values in the column and returns 14321.
- RIGHT(SUMPRODUCT(VALUE(MID(B5&”1″,ROW(INDIRECT(“1:”&LEN(B5&”1”))),1))*10^(ROW(INDIRECT(“1:”&LEN(B5&”1”)))-1)),LEN(B5)): Returns 4321 from the sum.
3. Utilizing TEXTJOIN Function
The TEXTJOIN function in excel concatenates multiple values with or without a delimiter. This function can help us to reverse a number in excel. Let us see how.
Steps:
- To begin this method, again click on cell C5 and type the following formula:
=TEXTJOIN("",1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1))
- Finally, click on any cell and copy this formula to the cells below. This will give you the numbers in the reverse format.
🔎 How Does the Formula Work?
- MID(B5,{10,9,8,7,6,5,4,3,2,1},1): Puts the numbers in 1234 each in different cells in a reversed order.
- TEXTJOIN(“”,1,MID(B5,{10,9,8,7,6,5,4,3,2,1},1)): Joins the each number in a cell in the reversed order.
4. Reverse a Number Using Dynamic Array
The Dynamic Array in excel allows working with multiple values at the same time in a formula. We can use this function to quickly reverse a number in excel.
Steps:
- First, navigate to cell C5 and insert this formula:
=TEXTJOIN("",1,MID(B5,ABS(ROW(INDIRECT("1:"&LEN(B5)))-(LEN(B5)+1)),1))
- Now, press the Enter key and copy this formula to all the cells below.
- As a result, this will reverse your numbers.
🔎 How Does the Formula Work?
- LEN(B5)+1: This returns the number 5.
- ABS(ROW(INDIRECT(“1:”&LEN(B5)))-(LEN(B5)+1)): Reverses the number 1234 and puts it in a column with each character in different cell.
- TEXTJOIN(“”,1,MID(B5,ABS(ROW(INDIRECT(“1:”&LEN(B5)))-(LEN(B5)+1)),1)): Joins the each number in a cell in the reversed order.
5. Applying SEQUENCE Function to Reverse a Number
The SEQUENCE function in excel can generate a list of sequential numbers in an array. We can then use this array to reverse the number.
Steps:
- To start with, go to cell C5 as previously and enter the following formula:
=TEXTJOIN("",1,MID(B5,SEQUENCE(LEN(B5),,LEN(B5),-1),1))
- Now, press the Enter key and copy this formula to the cells below.
- Consequently, you should get the reverse form of all the numbers.
🔎 How Does the Formula Work?
- SEQUENCE(LEN(B5),,LEN(B5),-1): Puts the number 4321 in a column in 4
- TEXTJOIN(“”,1,MID(B5,SEQUENCE(LEN(B5),,LEN(B5),-1),1)): Joins the each number in a cell in the reversed order.
Read More: How to Reverse a String in Excel
6. Reversing Number in Excel by VBA Code
If you have a large dataset of numbers that you want to reverse, then VBA is possibly the best option available. We will see here how we can easily reverse a number in excel using VBA.
Steps:
- Firstly, go to the Developer tab and click on Visual Basic.
- Now, in the VBA window, click on Insert and then Module.
- Next, in the module window, type in the code below:
Public Sub Reverse()
Dim sResult As String
sResult = StrReverse("1234")
Range("C5").Value = sResult
End Sub
- Here, under the Developer tab, click on Macros.
- Now, in the Macro window, select the macro we created and click on Run.
- As a result, the VBA code will generate the reverse form of all the numbers.
Read More: How to Use Excel VBA to Reverse String
How to Reverse Number Signs (Positive or Negative) in Excel
If you are doing any mathematical calculations inside excel, then you might need to change the signs of the numbers. We can do this very easily by following the steps below.
Steps:
- First, click on cell C5 and enter the following formula:
=(-1)*B5
- Finally, press Enter and copy this formula to the other necessary cells which should reverse their signs.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that the methods I showed above on how to reverse a number in excel were helpful to you. As you can see, reversing a number in excel can be done in many ways. So you have to choose the one that best suits your needs. If you get stuck at any of the steps, make sure to go through the steps a few times to clear up any confusion. If you have any queries, please let me know in the comments.
