Convert Base 36 to base 10
David
36- alpha numeric - I need it in base 10- number format- I have approx 30,000
of these!- is ther a formula to convert from 1 to another?
--
Thanks for your help
Rick Rothstein
"digits" are these...
0, 1, 2, ...., 9, A, B, ..., X, Y, Z
and if that is the case, then you actually have Base 37 numbers and not Base
36. For Base36 numbers, the letter Z would not be in your set of digits... Z
would be the 37th digit because 0 is the first digit. Assuming you really
have Base36 numbers (no Z), then this function should do what you want...
Function ConvertBase36ToBase10(Base36Number As String) As Long
Dim X As Long, Total As Long, Digit As String
For X = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, X, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10 + IIf(IsNumeric(Digit), _
Digit, Asc(Digit) - 54) * 36 ^ (Len(Base36Number) - X)
Next
End Function
If Z is in your set (meaning you have Base37 numbers), then simply change
all the 36's to 37's.
--
Rick (MVP - Excel)
"David" <Da...@discussions.microsoft.com> wrote in message
news:23328038-FEB3-461D...@microsoft.com...
Joe User
Option Explicit
Function HexTri2Dec(s As String)
Dim c As String * 1, bNeg As Boolean
Dim i As Long, f As Double, d As Double, x As Long
s = Trim(s)
If Mid(s, 1, 1) = "-" Then
If Len(s) = 1 Then GoTo badForm
bNeg = True: i = 2
Else
bNeg = False: i = 1
End If
c = ""
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If c = "." Then Exit For
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
d = d * 36 + x
If Err.Number <> 0 Then GoTo badNum
Next i
If c = "." Then
f = 1
For i = i + 1 To Len(s)
c = LCase(Mid(s, i, 1))
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
f = f * 36
If Err.Number <> 0 Then GoTo done
d = d + x / f
Next i
End If
done:
If bNeg Then d = -d
HexTri2Dec = d
Exit Function
badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function
badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function
----- original message -----
"Joe User" wrote:
> "David" wrote:
> > I have been given a spreadsheet with transaction
> > numbers converted into base 36- alpha numeric
> > - I need it in base 10- number format- I have
> > approx 30,000 of these!- is ther a formula to
> > convert from 1 to another?
>
> Do you really mean base 16 -- hexadecimal? Are the digits only 0-9 and A-F?
>
> If so, then use HEX2DEC below. But note the restrictions explained in the
> Help page. If they do not work for you, you can modify the UDF below,
> changing 36 to 10 and changing the sHetTri string among other cosmetic
> changes.
>
> If you truly mean hexatridecimal -- digits 0-9 and A-Z -- use the HexTri2Dec
> UDF below.
>
> In either case, if the 30,000 hex-whatever numbers are in a single column,
> enter the follow formula, whichever applies, in a parallel column:
>
> =HEX2DEC(A1)
>
> =HexTri2Dec(A1)
>
> If you wish, you can copy the new column, use paste-special-value to replace
> the original column, then delete the new column.
>
> UDF....
>
> Option Explicit
>
> Function HexTri2Dec(s As String)
> Const sHexTri As String * 36 = _
> "0123456789abcdefghijklmnopqrstuvwxyz"
> Dim c As String * 1, bNeg As Boolean
> Dim i As Long, f As Double, d As Double, x As Long
> s = Trim(s)
> If Mid(s, 1, 1) = "-" Then
> If Len(s) = 1 Then GoTo badForm
> bNeg = True: i = 2
> Else
> bNeg = False: i = 1
> End If
> c = ""
> On Error Resume Next
> For i = i To Len(s)
> c = LCase(Mid(s, i, 1))
> If c = "." Then Exit For
> x = InStr(sHexTri, c)
> If x = 0 Then GoTo badForm
> d = d * 36 + x - 1
> If Err.Number <> 0 Then GoTo badNum
> Next i
> If c = "." Then
> f = 1
> For i = i + 1 To Len(s)
> c = LCase(Mid(s, i, 1))
> x = InStr(sHexTri, c)
> If x = 0 Then GoTo badForm
> f = f * 36
> If Err.Number <> 0 Then GoTo done
> d = d + (x - 1) / f
> Next i
> End If
>
> done:
> If bNeg Then d = -d
> HexTri2Dec = d
> Exit Function
>
> badNum:
> HexTri2Dec = CVErr(xlErrNum)
> Exit Function
>
> badForm:
> HexTri2Dec = CVErr(xlErrValue)
> End Function
Joe User
> Are you sure your numbers are Base 36? I ask because I kind of suspect
> your "digits" are these...
> 0, 1, 2, ...., 9, A, B, ..., X, Y, Z
> and if that is the case, then you actually have Base 37
No, that's base 36. A thru Z represent the 26 values 10 thru 35. Google
"base 36" or see http://en.wikipedia.org/wiki/Base_36 .
------ original message -----
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uWTGQ2Oq...@TK2MSFTNGP06.phx.gbl...
Ron Rosenfeld
<rick.new...@NO.SPAMverizon.net> wrote:
>Are you sure your numbers are Base 36? I ask because I kind of suspect your
>"digits" are these...
>
>0, 1, 2, ...., 9, A, B, ..., X, Y, Z
>
>and if that is the case, then you actually have Base 37 numbers and not Base
>36. For Base36 numbers, the letter Z would not be in your set of digits... Z
>would be the 37th digit because 0 is the first digit. Assuming you really
>have Base36 numbers (no Z), then this function should do what you want...
Are you sure about that Rick?
It seems to me that 10 digits (0-9) + 26 [A-Z] letters --> Base 36
--ron
Rick Rothstein
David... this function will do what you want...
Function ConvertBase36ToBase10(Base36Number As String) As Long
Dim X As Long, Total As Long, Digit As String
For X = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, X, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10 + IIf(IsNumeric(Digit), _
Digit, (Asc(Digit) - 55)) * (36 ^ (Len(Base36Number) - X))
Next
End Function
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uWTGQ2Oq...@TK2MSFTNGP06.phx.gbl...
David
I have tried the code what I get is for a sample code
O81D8KEURD94I = #value
but
081d8ke = 486026654
Is there any length critera in the function- couldn't spot any
--
Thanks for your help
"Rick Rothstein" wrote:
> .
>
Martin Brown
> Guys thanks for this-
>
> I have tried the code what I get is for a sample code
> O81D8KEURD94I = #value
> but
> 081d8ke = 486026654
>
> Is there any length critera in the function- couldn't spot any
>
Yes. Implicit in the declaration of Long values -2^31 < x <= 2^31-1
You have to explicitly implement some form of long integer arithmetic to
handle values which go outside this boundary.
Decimal 2146483647 is the largest Long value which in Base36 = ZIK0ZJ
(subject to typos)
You could cut the string into two parts and pray that the leading digit
is always zero. Unsigned integers can handle 6 base36 digits OK.
The mantissa of Double precision reals would let you do up to 9 digits
of Base36.
Regards,
Martin Brown
Joe User
> I have tried the code what I get is for a sample code
> O81D8KEURD94I = #value
If you had tried my HexTri2Dec function, you wouldn't have gotten that
problem.
----- original message ------
Ron Rosenfeld
wrote:
>Guys thanks for this-
>
>I have tried the code what I get is for a sample code
>O81D8KEURD94I = #value
>but
>081d8ke = 486026654
>
>Is there any length critera in the function- couldn't spot any
You get a VALUE error because Rick Dim'd is variables as Longs, and your first
entry overflows that.
If you change it to Double, it should work OK:
===================
Function ConvertBase36ToBase10(Base36Number As String) As Double
Dim X As Long, Total As Double, Digit As String
For X = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, X, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10 + IIf(IsNumeric(Digit), _
Digit, (Asc(Digit) - 55)) * (36 ^ (Len(Base36Number) - X))
Next
End Function
=====================
Of course, Excel is limited to 15 digit precision. You can get increased
precision in VBA by using the Decimal data type, but the only way to get that
into a worksheet cell would be with a string output.
--ron
Joe User
> "David" wrote:
> > I have tried the code what I get is for a sample code
> > O81D8KEURD94I = #value
>
> If you had tried my HexTri2Dec function, you wouldn't
> have gotten that problem.
Although my function would do the best we can in converting O81D8KEURD94I, I
should point that the base10 equivalent is about 1.14778E+20. Since that is
more than 15 digits, it cannot be represented exactly as an Excel number.
Since these are transaction ids, not numbers to be used in arithmetic, it
would be better to use a UDF that returns the exact conversion as text.
Caveat: Someone might suggest using VBA type Decimal instead of Double.
That would indeed work for this example. However, it is not a general
solution, being limited to 28-digit integers (and some 29-digit integers).
Nevertheless, below is my UDF with that modification. For your example,
the result is the string 114779126356831142514.
Note: This implementation allows only integer base36 numbers.
UDF....
Option Explicit
Function HexTri2Dec(s As String)
Dim c As String * 1, bNeg As Boolean
Dim i As Long, x As Long, d
s = Trim(s)
If Mid(s, 1, 1) = "-" Then
If Len(s) = 1 Then GoTo badForm
bNeg = True: i = 2
Else
bNeg = False: i = 1
End If
c = ""
d = CDec(0)
On Error Resume Next
For i = i To Len(s)
c = LCase(Mid(s, i, 1))
If "0" <= c And c <= "9" Then x = Asc(c) - 48 _
Else If "a" <= c And c <= "z" Then x = Asc(c) - 87 _
Else: GoTo badForm
d = d * 36 + x
If Err.Number <> 0 Then GoTo badNum
Next i
done:
If bNeg Then d = -d
HexTri2Dec = Format(d, "0") 'allow only integers
Exit Function
badNum:
HexTri2Dec = CVErr(xlErrNum)
Exit Function
badForm:
HexTri2Dec = CVErr(xlErrValue)
End Function
----- original message ------
Rick Rothstein
a 28-digit Base36 number (max "number" is ZZZZZZZZZZZZZZZZZZ)...
Function ConvertBase36ToBase10(Base36Number As String) As Variant
Dim x As Long, Digit As String, Power As Variant
If Len(Base36Number) > 18 Or Base36Number Like "*[!0-9A-Za-z]*" Then
ConvertBase36ToBase10 = CVErr(xlErrNum)
Exit Function
End If
For x = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, x, 1))
If Len(Base36Number) > 9 Then
Power = CDec("101559956668416") * (36 ^ (Len(Base36Number) - 9 - x))
Else
Power = 36 ^ (Len(Base36Number) - x)
End If
ConvertBase36ToBase10 = ConvertBase36ToBase10 + CDec(IIf(IsNumeric( _
Digit), Digit, (Asc(Digit) - 55)) * Power)
Next
End Function
Note that the If..Then handling of the exponent for the 36 base number is
necessary because raising any number to a power using the caret (^(^(^)
operator collapses Decimal Data Type values back to Long Data Type values...
the 101559956668416 value is 36 raised to the 9th power. I also through in
some error checking as well.
--
Rick (MVP - Excel)
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:24m2n5t9fnigourdm...@4ax.com...
Martin Brown
It may be cleaner to avoid ^ entirely and to do the loop incrementally -
something along the lines of
ConvertBase36ToBase10 = 0
For x = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, x, 1))
ConvertBase36ToBase10 = ConvertBase36ToBase10*36 +
CDec(IIf(IsNumeric(Digit), Digit, (Asc(Digit) - 55))
Next
Regards,
Martin Brown
Rick Rothstein
Function ConvertBase36ToBase10(Base36Number As String) As Variant
Dim x As Long, Digit As String, Power As Variant
If Len(Base36Number) > 18 Or Base36Number Like "*[!0-9A-Za-z]*" Then
ConvertBase36ToBase10 = CVErr(xlErrNum)
Exit Function
End If
For x = Len(Base36Number) To 1 Step -1
Digit = UCase(Mid(Base36Number, x, 1))
If Len(Base36Number) > 9 Then
Power = CDec("101559956668416") * (36 ^ (Len(Base36Number) - 9 - x))
Else
Power = 36 ^ (Len(Base36Number) - x)
End If
ConvertBase36ToBase10 = ConvertBase36ToBase10 + CDec(IIf(IsNumeric( _
Digit), Digit, (Asc(Digit) - 55)) * Power)
Next
End Function
Note that I also added some error checking as well.
--
Rick (MVP - Excel)
"David" <Da...@discussions.microsoft.com> wrote in message
news:DAEC194E-4145-4AA9...@microsoft.com...
Rick Rothstein
"through"??? That should have been "threw" instead.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.new...@NO.SPAMverizon.net> wrote in message
news:OOhXi9Zq...@TK2MSFTNGP06.phx.gbl...
David
Thanks very much for all that- works perfectly
--
cardonli...@gmail.com
I saw your posts. Thanks for those.
But I feel 0,1,2,...9 and A,B,C till Z adds up to 10 plus 26 equals 36 and not 37 as you have mentioned.
Please tell me where am I going wrong.
Thanks a lot.
Rajeev
joeu2004
> On Monday, 8 February 2010 22:40:01 UTC+5:30, David wrote:
>> I have been given a spreadsheet with transaction numbers
>> converted into base 36
> I saw your posts. Thanks for those. But I feel 0,1,2,...9
> and A,B,C till Z adds up to 10 plus 26 equals 36 and not 37
> as you have mentioned. Please tell me where am I going wrong.
First, you are responding to comments made 4 years ago. Obviously, the
discussion is stale (read: dead).
Second, you fail to quote the comments you are responding to, namely Rick's.
So we have no context.
Finally, you are repeating comments that were already made in the 4-year-old
discussion, and Rick admitted his mistake.
Here is the complete context.
Rick wrote:
> Are you sure your numbers are Base 36? I ask because I kind of
> suspect your "digits" are these...
> 0, 1, 2, ...., 9, A, B, ..., X, Y, Z
> and if that is the case, then you actually have Base 37 numbers
> and not Base 36.
> No, that's base 36. A thru Z represent the 26 values 10 thru 35.
> Google "base 36" or see http://en.wikipedia.org/wiki/Base_36 .