Convert from base 16 to base 62 and possible limits of VBA

Hello,


I am currently trying to find a way to convert from base 16 to base 62 (and the other way around) [Yes base 62 is correct! NOT base 64] but from what I could gather this seems to be a bit tricky because of the restrictions in VBA concerning very large numbers.

An example would look like this:
0551a7be6aa14c6d93d014b02c4ec40d (base 16)
A2HFM1ZYZLfqTOc9Iv1cT (base 62)

As you can see the numbers are quite large.
I tried converting to base 10 first and then base 16 or base 62 but that did not work.

Does anyone ever encounter a similar problem and is willing to give me some hints on how to solve this?


Thank you,
LPLA

62? Are you sure you do not want base 64? I have never heard of any practical use for base 62.

Originally Posted by 6StringJazzer

62? Are you sure you do not want base 64? I have never heard of any practical use for base 62.

Nor me........!

Googling threw up "shortening URLs"

My first google attempts are of the form "wiki base62".
It immediately finds https://en.wikipedia.org/wiki/Base62.
And the google search "convert to base62" turns up some algorithms that might (or might not) be translatable into VBA.
Good luck with that!!

Base 62 is used for URL shortening, which I was not aware of. And it makes perfect sense since it uses exactly the set of characters that can be used in a (case-sensitive) URL.

I suspect that due to the length this will need an algorithm that deals with chunks of character strings rather than trying to treat the string as a single number, which would be quite large. A base 62 number will exceed the Long data type at about 11 digits. I do not have experience with those types of calculations but I'm sure it's been done in other languages for web development. I'll see if I can find something that can be converted to VBA.

Yes, base62 __can_be__ used for URL encoding. But that is not its only use. "All men are Greek, but not all Greeks are men".

Using a variety of online sources, I have concluded that base16 0551a7be6aa14c6d93d014b02c4ec40d is converted to base62 A2HFM1ZYZLfqTOc9Iv1cT by interpreting the base16 encoding as a representation of an integer, namely base10 7070118273220996960834318043337114637.

The following describes how I reached that conclusion, and I provide a VBA __prototype__ of an implementation of the conversion, as "proof of concept".

PS.... That is not the only way to interpret the problem. I would have interpreted it differently, treating the data as a bit stream. But then, the base62 encoding would have been very different. OTOH, perhaps LPLA's example is incorrect, based on his misinterpretation and online converters that he stumbled across, as I did.

@LPLA, if there is any doubt, please provide some context for the conversion. That is, what are you doing that requires the base62 conversion of that data? For example, encoding blockchains?

-----

Caveat: I reference several websites. But I cannot vouch for their correctness.

In fact, note that https://en.wikipedia.org/wiki/Base62 is ambiguous.

The conversion table indicates that 0 through 61 correspond to the characters ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789.

But in fact(?), 0 through 61 correspond to 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz, as implied at the beginning of the article.

The latter is consistent with the online base62 converter cited below, as well as with some online sources and the example in the OP. Moreover, it is consistent with what I would expect.

-----

When I enter base16 0551a7be6aa14c6d93d014b02c4ec40d, with and without the leading zero, into the base62 converter at https://math.tools/calculator/base/16-62, the result is indeed base62 A2HFM1ZYZLfqTOc9Iv1cT.

And when I enter the base16 value into the hex-to-decimal converter at https://www.scopulus.co.uk/tools/hexconverter.htm, the result is base10 7070118273220996960834318043337114637.

I cannot demonstrate that base10 7070118273220996960834318043337114637 can be converted to base16 551a7be6aa14c6d93d014b02c4ec40d and base62 A2HFM1ZYZLfqTOc9Iv1cT, because the base10 value is too large even for VBA type Decimal.

However, I can demonstrate the conversions by using the highlighted subset, base10 73220996960834318043337114637.

Enter that base10 value at https://www.scopulus.co.uk/tools/hexconverter.htm, and we get base16 EC96FD36D315FCF56C4EC40D.

Enter that base16 value at https://math.tools/calculator/base/16-62, and we get base62 1XE4kWaxLeHgOKoQP.

The following VBA procedure demonstrates the calculations.

Option Explicit

Sub doit()
' https://www.scopulus.co.uk/tools/hexconverter.htm
' converts base16 to base10 as follows:
' b16 = "0551a7be6aa14c6d93d014b02c4ec40d"
' b10 = "7070118273220996960834318043337114637"  ' exceeds CDec limit
'
' https://math.tools/calculator/base/16-62
' converts base16 to base62 as follows:
' b16 = "0551a7be6aa14c6d93d014b02c4ec40d"  ' with and without leading zero
' b62 = "A2HFM1ZYZLfqTOc9Iv1cT"
'
'... try a smaller number ...

Dim b10 As Variant
b10 = CDec("73220996960834318043337114637")         ' const

Const b16 As String = "EC96FD36D315FCF56C4EC40D"    ' https://www.scopulus.co.uk/tools/hexconverter.htm
Const b62 As String = "1XE4kWaxLeHgOKoQP"           ' https://math.tools/calculator/base/16-62

Dim x As Variant, xInt As Variant, xMod As Variant, i As Long

' base16 to base10 conversion
Dim x10 As Variant, c As String * 1, ci As Long
x10 = CDec(0)
For i = 1 To Len(b16)
    c = LCase(Mid(b16, i, 1))
    If "0" <= c And c <= "9" Then ci = Asc(c) - Asc("0") Else ci = Asc(c) - Asc("a") + 10
    x10 = x10 * 16 + ci
Next

' base10 to base62 conversion
Const c62 As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
Dim x62 As String
x = x10
Do
    xInt = Int(x / 62)
    xMod = x - xInt * 62
    x62 = Mid(c62, xMod + 1, 1) & x62
    x = xInt
Loop Until x = 0

' **** not needed ****
' base10 to base16 conversion
Const c16 As String = "0123456789ABCDEF"
Dim x16 As String
x = b10
Do
    xInt = Int(x / 16)
    xMod = x - xInt * 16
    x16 = Mid(c16, xMod + 1, 1) & x16
    x = xInt
Loop Until x = 0

Dim s As String
s = "b16: " & b16 & _
    vbNewLine & "x16: " & x16 & _
    vbNewLine & "b10: " & b10 & _
    vbNewLine & "x10: " & x10 & _
    vbNewLine & "b62: " & b62 & _
    vbNewLine & "x62: " & x62
Debug.Print s
MsgBox s
End Sub

The next step is to implement a multiprecision version.

"The exercise is left for the student".

PS.... There are third-party add-ons for doing basic arithmetic with very large numbers. I cannot vouch for any of them.

Originally Posted by LPLA

I don't have much time right now to thoroughly read your post but I will do so asap and answer (or edit this post).

Please post new responses. Do not edit the last response. It is difficult to discover that a posting has been edited.

( "Do as I say, not as I do". )

In any case, a "thorough reading" might not be productive.

What I posted is a "proof of concept" to demonstrate the __form__ of an algorithm to convert long integers, as I thought the encodings represent.

But the algorithm is not useful if you are not willing to install (or we cannot implement) functions that perform basic arithmetic on very large numbers. (The difficult one is division.)


------

Originally Posted by LPLA

I need the conversion to compare and verify IDs. The provider used to send them in the same base but for some reason decided to switch. Now we are left with IDs in base 16 and base 62 which we of course can't compare.

First, please be specific in your descriptions. Which is it:

1. The provider used to send IDs in base62, and now they are sending IDs in base16?

2. Or the provider used to send IDs in base16, and now they are sending IDs in base62?

3. And what form of IDs do you store: whatever encoding the provider used to send; or something else (what)?

4. Do __you__ ever originate encoded IDs from raw data (or very large integers)?

5. Or are the encoded IDs (base16, base62) always generated originally by the provider, and you merely store them for the purpose of comparisons later?

More to the point: do you really need to convert base16 to base62 or base62 to base16?

For comparison purposes, it is easier to convert base16 to base10 and base62 to base10, then compare the base10 IDs. ( No need for multiprecision division. )

Would that meet your needs?

-----

Originally Posted by LPLA

I am trying to get rid of any third party conversion tools or sites and have them automatically converted in Excel.

I understand that you do not want to use separate applications.

But what about __third_party__ add-ins that become __part_of__ your Excel file?

I could understand if that is not allowed, as well.

Some contractual agreements do not permit the use of software that cannot be "audited" and that is not well-accepted "mainstream" products, like Excel.

(And some contracts even require documentation of algorithms for some Excel functionality!)

You can use the famous GMP library (The GNU Multiple Precision Arithmetic Library) for this purpose ( https://gmplib.org/ ).

The limitation of how big the integer is only the available RAM on your PC :
"There is no practical limit to the precision except the ones implied by the available memory in the machine GMP runs on."

And as a bonus, there is a builtin method to pull the number in any base number from 2 to 62 ( https://gmplib.org/manual/Converting-Integers ) :
Function: char * mpz_get_str (char *str, int base, const mpz_t op)
Convert op to a string of digits in base base. The base argument may vary from 2 to 62 or from -2 to -36.

I have make an exe file using this library which you can use (included in the attachment), and an Excel UDF as wrapper to call this program.

And a sample sub (using sample numbers from your post #1) :

Sub Test()
  Debug.Print ConvertBase(16, 62, "0551a7be6aa14c6d93d014b02c4ec40d")
  Debug.Print ConvertBase(62, 16, "A2HFM1ZYZLfqTOc9Iv1cT")

  Debug.Print ConvertBase(10, 16, "255")
  Debug.Print ConvertBase(16, 10, "FF")

  Debug.Print ConvertBase(10, 2, "170")
End Sub