How to Reverse a Number in Excel (6 Quick Tricks)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to reverse a number in excel


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

entering SUM and VALUE formula to reverse a number in excel

  • 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))

entering the SUMPRODUCT formula to reverse a number in excel

  • 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))

entering the TEXTJOIN formula to reverse a number in excel

  • 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))

entering dynamic array formula to reverse a number in excel

  • 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))

entering the SEQUENCE function to reverse a number in excel

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

opening the VBA window to reverse a number in excel

  • Now, in the VBA window, click on Insert and then Module.

inserting a new module to reverse a number in excel

  • 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

VBA code to reverse a number in excel

  • 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

How to Reverse Number Signs Positive Negative in Excel

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


Related Articles


<< Go Back to Excel Reverse Order | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo