Convert Base 58 to Base 10 and vice versa

[DavidRoger]

I have been given a list of Base 58. I need to convert that to Base 10. The
biggest of Base 58 number is 35408922396. Is there a formula that can be used to get the conversion done?

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
You could do this with a user-defined formula, but since there is not a standard character sequence for base58, you will need to know whether your upper case or lower case letters come first. A quick web search found this site http://icoloma.blogspot.com/2010/03/create-your-own-bitly-using-base58.html that describes both just a bit, and has some non-Excel code for converting to Base 58. I wrote a Base 36 to Decimal conversion function some time ago...you should be able to use the same concept for Base 58. I wrote this nearly 15 years ago, and might code it differently now. In particular, I would probably make NumSize based on the length of the input string rather than fixing it at 7.

[FONT=Arial]Function B36ToDec(B36Str)[/FONT]
[FONT=Arial]NumSize = 7  'this is the maximum length of the input string[/FONT]
[FONT=Arial]Dim Temp()[/FONT]
[FONT=Arial]Dim Final()[/FONT]
[FONT=Arial]ReDim Temp(NumSize)[/FONT]
[FONT=Arial]ReDim Final(NumSize)[/FONT]
[FONT=Arial]Sum = 0[/FONT]
[FONT=Arial]B36Set = "0123456789abcdefghijklmnopqrstuvwxyz"[/FONT]
[FONT=Arial]For i = 1 To NumSize[/FONT]
[FONT=Arial]    Temp(i) = InStr(B36Set, Mid(B36Str, i, 1)) - 1[/FONT]
[FONT=Arial]    Final(i) = Temp(i) * 36 ^ (NumSize - i)[/FONT]
[FONT=Arial]    Sum = Sum + Final(i)[/FONT]
[FONT=Arial]'    MsgBox (Str(i) + ", " + Mid(B36Str, i, 1) + ", " + Str(Sum))[/FONT]
[FONT=Arial]Next i[/FONT]
[FONT=Arial]B36ToDec = Sum[/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>[FONT=Arial] [/FONT]</o:p>
[FONT=Arial]End Function[/FONT]

Hope this helps,
Cindy

 

[shg]

What symbols are used in the base 58 numbers?

 

[Cindy Ellis]

0-9 and upper/lower a-z, minus 2 pairs that can be confused easily: if I recall correctly, the missing characters are lower case L, upper case i , zero, and upper case o. It seems that some algorithms put upper case before lower case, and some put

lower case before upper. (It's used to create "tiny" urls).

 

[wigi]

I wrote this nearly 15 years ago, and might code it differently now.

[FONT=Arial]Function B36ToDec(B36Str)[/FONT]
[FONT=Arial]NumSize = 7  'this is the maximum length of the input string[/FONT]
[FONT=Arial]Dim Temp()[/FONT]
[FONT=Arial]Dim Final()[/FONT]
[FONT=Arial]ReDim Temp(NumSize)[/FONT]
[FONT=Arial]ReDim Final(NumSize)[/FONT]
[FONT=Arial]Sum = 0[/FONT]
[FONT=Arial]B36Set = "0123456789abcdefghijklmnopqrstuvwxyz"[/FONT]
[FONT=Arial]For i = 1 To NumSize[/FONT]
[FONT=Arial]    Temp(i) = InStr(B36Set, Mid(B36Str, i, 1)) - 1[/FONT]
[FONT=Arial]    Final(i) = Temp(i) * 36 ^ (NumSize - i)[/FONT]
[FONT=Arial]    Sum = Sum + Final(i)[/FONT]
[FONT=Arial]'    MsgBox (Str(i) + ", " + Mid(B36Str, i, 1) + ", " + Str(Sum))[/FONT]
[FONT=Arial]Next i[/FONT]
[FONT=Arial]B36ToDec = Sum[/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>[FONT=Arial] [/FONT]</o:p>
[FONT=Arial]End Function[/FONT]

Hope this helps,
Cindy

Hello Cindy,

yes, for instance:

Function B36ToDec(B36Str)
    B36Set = "0123456789abcdefghijklmnopqrstuvwxyz"
    For i = 1 To Len(B36Str)
        B36ToDec = B36ToDec + (InStr(B36Set, Mid(B36Str, i, 1)) - 1) * 36 ^ (Len(B36Str) - i)
    Next
End Function

 

[shg]

Thanks, Cindy.

A formula alternative, but limited to converting something around 7zzzzzzzz

=SUMPRODUCT(
FIND(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1), "123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz") - 1,
58 ^ (LEN(A1) - ROW(INDIRECT("1:" & LEN(A1)))))

 

[DavidRoger]

In particular, I would probably make NumSize based on the length of the input string rather than fixing it at 7.

Hi Cindy,

Thank you for your reply.

Just to confirm with you.

NumSize = 7 change to 11 in my case.

 

[Cindy Ellis]

The code provided wigi just after mine replaces and simplifies the code I wrote, and accomplishes exactly the same thing.
Adjusting for Base 58, and depending on the character set sequence you would use (not tested, since I don't have anything to compare to):

Function B58ToDec(B58Str)
    B58Set = "123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ"
    For i = 1 To Len(B58Str)
        B58ToDec = B58ToDec + (InStr(B58Set, Mid(B58Str, i, 1)) - 1) * 58 ^ (Len(B58Str) - i)
    Next
End Function

There may be a problem with this due to the size of the numbers you're dealing with. 58^10 has more than 15 digits, so something's going to get lost in the process.

Cindy

 

[DavidRoger]

Hi Wigi,

Tested what Cindy suggested. Result not good.

Do you mind converting your code to Base 58?

 

[Cindy Ellis]

Hi,
Can you be more specific than just "not good"? My example literally converted wigi's code to what would have been base 58, with the caveats mentioned earlier (magnitude of the output and the order of the upper/lower case letters.
Can you please provide an example of some inputs and expected outputs?
That way we can test the code we provide before we post it.
Thanks,
Cindy