reverse a number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Some one may have done this but I am looking at working out palindromes on
excel and I am stuck at the first stage. I need to reverse a number value,
example 12 is 21.

Here is an example of a palindrome

87+78=165
165+561=726
726+627=1353
1353+3531=4884
 
You could use this User Defined Function (UDF)
Open the VB Editor (ALT+F11), Insert>Module, and paste this code in the code
window.
You can now use the function from your worksheet

' ================================
Function ReverseNumber(a As String) As Long
Dim i As Long
Dim b As String
For i = Len(a) To 1 Step -1
b = b + Mid$(a, i, 1)
Next i
ReverseNumber = b
End Function
' ================================


--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
But if you only need a formula then
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),(10^(ROW(INDIRECT("1:"&LEN(A1))))))/10
where cell A1 holds the value to be transposed/swapped
So 561 in A1 will become 165
 
Try this,
Enter with Ctrl+Shift+Enter

Enter the number you want to reverse into cell "A1" and then enter the
following array formula into cell "B1".

=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))Eamon"Derek" <[email protected]> wrote in messageSome one may have done this but I am looking at working out palindromes on> excel and I am stuck at the first stage. I need to reverse a number value,> example 12 is 21.>> Here is an example of a palindrome>> 87+78=165> 165+561=726> 726+627=1353> 1353+3531=4884
 
Try this,
Enter with Ctrl+Shift+Enter

Enter the number you want to reverse into cell "A1" and then enter the
following array formula into cell "B1".

=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))Eamon"Derek" <[email protected]> wrote inmessageSome onemay have done this but I am looking at working out palindromes on> excel andI am stuck at the first stage. I need to reverse a number value,> example 12is 21.>> Here is an example of a palindrome>> 87+78=165> 165+561=726>726+627=1353> 1353+3531=4884>
 
Very clever! I'm afraid I'll never get the hang of SUMPRODUCT formulas,
especially those with "--"
I used to have a colleague who said "You call that clever? That's a brain
defect!" <g>

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
Maybe another vba option:

Function PalindromeAdd(n)
PalindromeAdd = n + StrReverse(CStr(n))
End Function

?PalindromeAdd(1353)
4884

It's too bad they don't follow a pattern that Excel can take advantage of.
A two digit number like "ab" can be written as
(10*a + b)
So,
(10*a + b) + (10*b + a)

equals
11*(a + b)

So, a number like 87 can be calculated as:
?11*(8+7)
165

But for larger numbers, it gets too complicated, afaik.
For example, a 4 digit number "abcd" would be: 1001*(a+d) + 110*(b + c)
 
Back
Top