Thread: Function to convert number base

1. Function to convert number base

A few years ago, HansV was kind enough to suggest some code through which I could convert Base16 numbers to Base32 and vise versa. I am talking about SHA-1 hash values. For example, I would use the function =Personal.xlsb!base16to32(A2) to convert (A2) E360AC4E8F0C4C7F6C6DE38140E8609508EA91A3 to (B2) 4NQKYTUPBRGH63DN4OAUB2DASUEOVEND. The code is below.

The problem, however, is that Excel doesn't like leading zeros. So, if (A2) 06914B70EEE2247FAE1F3902B35B363EA3ABA485 the function sets (B2) 2IUW4HO4ISH7LQ7HEBLGWZWH2R2XJEF, and it should be A2IUW4HO4ISH7LQ7HEBLGWZWH2R2XJEF. Excel is disregarding the leading zero. I tried doing some custom formatting to A2, but can't get anything to work.

snap014.jpg

Code:
```Function Base16To32(Base16 As Variant) As Variant
Dim Base2 As String
Dim i As Integer
Base16 = UCase(Base16)
For i = 1 To Len(Base16)
Select Case Mid(Base16, i, 1)
Case "0"
Base2 = Base2 & "0000"
Case "1"
Base2 = Base2 & "0001"
Case "2"
Base2 = Base2 & "0010"
Case "3"
Base2 = Base2 & "0011"
Case "4"
Base2 = Base2 & "0100"
Case "5"
Base2 = Base2 & "0101"
Case "6"
Base2 = Base2 & "0110"
Case "7"
Base2 = Base2 & "0111"
Case "8"
Base2 = Base2 & "1000"
Case "9"
Base2 = Base2 & "1001"
Case "A"
Base2 = Base2 & "1010"
Case "B"
Base2 = Base2 & "1011"
Case "C"
Base2 = Base2 & "1100"
Case "D"
Base2 = Base2 & "1101"
Case "E"
Base2 = Base2 & "1110"
Case "F"
Base2 = Base2 & "1111"
Case Else
Base16To32 = CVErr(xlErrValue)
Exit Function
End Select
Next i
If Not Len(Base2) Mod 5 = 0 Then
Base2 = String(5 - (Len(Base2) Mod 5), "0") & Base2
End If
If Len(Base2) > 5 And Left(Base2, 5) = "00000" Then
Base2 = Mid(Base2, 6)
End If
Base16To32 = ""
For i = 1 To Len(Base2) Step 5
Select Case Mid(Base2, i, 5)
Case "00000"
Base16To32 = Base16To32 & "A"
Case "00001"
Base16To32 = Base16To32 & "B"
Case "00010"
Base16To32 = Base16To32 & "C"
Case "00011"
Base16To32 = Base16To32 & "D"
Case "00100"
Base16To32 = Base16To32 & "E"
Case "00101"
Base16To32 = Base16To32 & "F"
Case "00110"
Base16To32 = Base16To32 & "G"
Case "00111"
Base16To32 = Base16To32 & "H"
Case "01000"
Base16To32 = Base16To32 & "I"
Case "01001"
Base16To32 = Base16To32 & "J"
Case "01010"
Base16To32 = Base16To32 & "K"
Case "01011"
Base16To32 = Base16To32 & "L"
Case "01100"
Base16To32 = Base16To32 & "M"
Case "01101"
Base16To32 = Base16To32 & "N"
Case "01110"
Base16To32 = Base16To32 & "O"
Case "01111"
Base16To32 = Base16To32 & "P"
Case "10000"
Base16To32 = Base16To32 & "Q"
Case "10001"
Base16To32 = Base16To32 & "R"
Case "10010"
Base16To32 = Base16To32 & "S"
Case "10011"
Base16To32 = Base16To32 & "T"
Case "10100"
Base16To32 = Base16To32 & "U"
Case "10101"
Base16To32 = Base16To32 & "V"
Case "10110"
Base16To32 = Base16To32 & "W"
Case "10111"
Base16To32 = Base16To32 & "X"
Case "11000"
Base16To32 = Base16To32 & "Y"
Case "11001"
Base16To32 = Base16To32 & "Z"
Case "11010"
Base16To32 = Base16To32 & "2"
Case "11011"
Base16To32 = Base16To32 & "3"
Case "11100"
Base16To32 = Base16To32 & "4"
Case "11101"
Base16To32 = Base16To32 & "5"
Case "11110"
Base16To32 = Base16To32 & "6"
Case "11111"
Base16To32 = Base16To32 & "7"
End Select
Next i
End Function

Function Base32To16(Base32 As Variant) As Variant
Dim Base2 As String
Dim i As Integer
Base32 = UCase(Base32)
For i = 1 To Len(Base32)
Select Case Mid(Base32, i, 1)
Case "A"
Base2 = Base2 & "00000"
Case "B"
Base2 = Base2 & "00001"
Case "C"
Base2 = Base2 & "00010"
Case "D"
Base2 = Base2 & "00011"
Case "E"
Base2 = Base2 & "00100"
Case "F"
Base2 = Base2 & "00101"
Case "G"
Base2 = Base2 & "00110"
Case "H"
Base2 = Base2 & "00111"
Case "I"
Base2 = Base2 & "01000"
Case "J"
Base2 = Base2 & "01001"
Case "K"
Base2 = Base2 & "01010"
Case "L"
Base2 = Base2 & "01011"
Case "M"
Base2 = Base2 & "01100"
Case "N"
Base2 = Base2 & "01101"
Case "O"
Base2 = Base2 & "01110"
Case "P"
Base2 = Base2 & "01111"
Case "Q"
Base2 = Base2 & "10000"
Case "R"
Base2 = Base2 & "10001"
Case "S"
Base2 = Base2 & "10010"
Case "T"
Base2 = Base2 & "10011"
Case "U"
Base2 = Base2 & "10100"
Case "V"
Base2 = Base2 & "10101"
Case "W"
Base2 = Base2 & "10110"
Case "X"
Base2 = Base2 & "10111"
Case "Y"
Base2 = Base2 & "11000"
Case "Z"
Base2 = Base2 & "11001"
Case "2"
Base2 = Base2 & "11010"
Case "3"
Base2 = Base2 & "11011"
Case "4"
Base2 = Base2 & "11100"
Case "5"
Base2 = Base2 & "11101"
Case "6"
Base2 = Base2 & "11110"
Case "7"
Base2 = Base2 & "11111"
Case Else
Base32To16 = CVErr(xlErrValue)
Exit Function
End Select
Next i
If Not Len(Base2) Mod 4 = 0 Then
Base2 = String(4 - (Len(Base2) Mod 4), "0") & Base2
End If
If Len(Base2) > 4 And Left(Base2, 4) = "0000" Then
Base2 = Mid(Base2, 5)
End If
Base32To16 = ""
For i = 1 To Len(Base2) Step 4
Select Case Mid(Base2, i, 4)
Case "0000"
Base32To16 = Base32To16 & "0"
Case "0001"
Base32To16 = Base32To16 & "1"
Case "0010"
Base32To16 = Base32To16 & "2"
Case "0011"
Base32To16 = Base32To16 & "3"
Case "0100"
Base32To16 = Base32To16 & "4"
Case "0101"
Base32To16 = Base32To16 & "5"
Case "0110"
Base32To16 = Base32To16 & "6"
Case "0111"
Base32To16 = Base32To16 & "7"
Case "1000"
Base32To16 = Base32To16 & "8"
Case "1001"
Base32To16 = Base32To16 & "9"
Case "1010"
Base32To16 = Base32To16 & "A"
Case "1011"
Base32To16 = Base32To16 & "B"
Case "1100"
Base32To16 = Base32To16 & "C"
Case "1101"
Base32To16 = Base32To16 & "D"
Case "1110"
Base32To16 = Base32To16 & "E"
Case "1111"
Base32To16 = Base32To16 & "F"
End Select
Next i
End Function```

I'll be grateful for some suggestions. Thanks!

2. Hi Jimmy,

I'm not quite following the situation. I do understand about number bases and how to convert between them, although I've never used base-32.

First, if you've entered a base-16 number which has hexadecimal values like a, c or f, Excel treats the cell as a General format. On my PC, the leading 0 was displayed. You can always change the format to text. So I'm not sure I understand the comment about Excel not liking leading 0's. That's true in general for an entry like 0435 but not something that contains letters. Of course, your base-16 number may only have decimal digits.

Second, I agree that custom formating won't do anything since formating applies only to what you see and not the actual value. So if you work with the value, like using Hans' macro, the leading zeros you see via a custom formating won't be present.

One of the things we used to do to create a leading zero was dependent on knowing the maximum length a value could take on. Suppose it was 999. We would add 1000 to all values. This was primarily for printing purposes. So when we went to print, we would print the right-most 3 digits of the value. For other purposes, such as computing, we'd have to be a bit more careful. To achieve both goals (printing leading zeros and computing), we could keep the original values but print the right-most 3 digits of 1000+variable.

Not sure if any of this helps but just some thoughts.

Fred

3. Thanks, Fred. Yes, Excel does retain the leading zero when I paste a Base16 hash value into a cell, as A3 in my example. The problem is that the function apparently ignores that leading zero when it converts (A3)Base16 to (B3)Base32. All of the values are SHA-1 hashes. While Base16 could consist entirely of numbers, it would be quite unusual, if not infeasible. Basically, the SHA-1 in hex consists of 20 bytes, or 40 alpha-numeric characters. When the function ignores the leading zero, it doesn't convert to Base32 accurately. Base 32 always contains letters and no zeros. However, if I convert Base32 to Base16 and the calculation requires the Base16 value to have a leading zero, Excel will not present the leading zero.

4. Jimmy,

If Len(Base2) > 5 And Left(Base2, 5) = "00000" Then
Base2 = Mid(Base2, 6)
End If

Comment out those lines and you will get the "A" in front.

HTH,
Maud

Code:
```Function Base16To32(Base16 As String) As Variant
Dim Base2 As String
Dim i As Integer
Base16 = UCase(Base16)
For i = 1 To Len(Base16)
Select Case Mid(Base16, i, 1)
Case "0"
Base2 = Base2 & "0000"
Case "1"
Base2 = Base2 & "0001"
Case "2"
Base2 = Base2 & "0010"
Case "3"
Base2 = Base2 & "0011"
Case "4"
Base2 = Base2 & "0100"
Case "5"
Base2 = Base2 & "0101"
Case "6"
Base2 = Base2 & "0110"
Case "7"
Base2 = Base2 & "0111"
Case "8"
Base2 = Base2 & "1000"
Case "9"
Base2 = Base2 & "1001"
Case "A"
Base2 = Base2 & "1010"
Case "B"
Base2 = Base2 & "1011"
Case "C"
Base2 = Base2 & "1100"
Case "D"
Base2 = Base2 & "1101"
Case "E"
Base2 = Base2 & "1110"
Case "F"
Base2 = Base2 & "1111"
Case Else
Base16To32 = CVErr(xlErrValue)
Exit Function
End Select
Next i
If Not Len(Base2) Mod 5 = 0 Then
Base2 = String(5 - (Len(Base2) Mod 5), "0") & Base2
End If
'If Len(Base2) > 5 And Left(Base2, 5) = "00000" Then
'Base2 = Mid(Base2, 6)
'End If
Base16To32 = ""
For i = 1 To Len(Base2) Step 5
Select Case Mid(Base2, i, 5)
Case "00000"
Base16To32 = Base16To32 & "A"
Case "00001"
Base16To32 = Base16To32 & "B"
Case "00010"
Base16To32 = Base16To32 & "C"
Case "00011"
Base16To32 = Base16To32 & "D"
Case "00100"
Base16To32 = Base16To32 & "E"
Case "00101"
Base16To32 = Base16To32 & "F"
Case "00110"
Base16To32 = Base16To32 & "G"
Case "00111"
Base16To32 = Base16To32 & "H"
Case "01000"
Base16To32 = Base16To32 & "I"
Case "01001"
Base16To32 = Base16To32 & "J"
Case "01010"
Base16To32 = Base16To32 & "K"
Case "01011"
Base16To32 = Base16To32 & "L"
Case "01100"
Base16To32 = Base16To32 & "M"
Case "01101"
Base16To32 = Base16To32 & "N"
Case "01110"
Base16To32 = Base16To32 & "O"
Case "01111"
Base16To32 = Base16To32 & "P"
Case "10000"
Base16To32 = Base16To32 & "Q"
Case "10001"
Base16To32 = Base16To32 & "R"
Case "10010"
Base16To32 = Base16To32 & "S"
Case "10011"
Base16To32 = Base16To32 & "T"
Case "10100"
Base16To32 = Base16To32 & "U"
Case "10101"
Base16To32 = Base16To32 & "V"
Case "10110"
Base16To32 = Base16To32 & "W"
Case "10111"
Base16To32 = Base16To32 & "X"
Case "11000"
Base16To32 = Base16To32 & "Y"
Case "11001"
Base16To32 = Base16To32 & "Z"
Case "11010"
Base16To32 = Base16To32 & "2"
Case "11011"
Base16To32 = Base16To32 & "3"
Case "11100"
Base16To32 = Base16To32 & "4"
Case "11101"
Base16To32 = Base16To32 & "5"
Case "11110"
Base16To32 = Base16To32 & "6"
Case "11111"
Base16To32 = Base16To32 & "7"
End Select
Next i
End Function```

5. The Following User Says Thank You to Maudibe For This Useful Post:

Jimmy-W (2014-08-29)

6. Maudibe, you are indeed a genius! Works perfectly, even when I have a Base16 with two leading zeros, e.g., 009FF744DB2C09A27525CC2EBC5F52179BBB988A.

However, I still have the problem going from Base32 to 16. For example, Base32 ACP7ORG3FQE2E5JFZQXLYX2SC6N3XGEK should convert to Base16 009FF744DB2C09A27525CC2EBC5F52179BBB988A. My code, Base32to16, returns 09FF744DB2C09A27525CC2EBC5F52179BBB988A, without the first leading zero. Might the code related to Base32to16 also be at fault? Thanks again!

7. Thanks Jimmy,

You are absolutely correct. You will have to comment out the following lines

If Len(Base2) > 4 And Left(Base2, 4) = "0000" Then
Base2 = Mid(Base2, 5)
End If

Code:
```Function Base32To16(Base32 As Variant) As Variant
Dim Base2 As String
Dim i As Integer
Base32 = UCase(Base32)
For i = 1 To Len(Base32)
Select Case Mid(Base32, i, 1)
Case "A"
Base2 = Base2 & "00000"
Case "B"
Base2 = Base2 & "00001"
Case "C"
Base2 = Base2 & "00010"
Case "D"
Base2 = Base2 & "00011"
Case "E"
Base2 = Base2 & "00100"
Case "F"
Base2 = Base2 & "00101"
Case "G"
Base2 = Base2 & "00110"
Case "H"
Base2 = Base2 & "00111"
Case "I"
Base2 = Base2 & "01000"
Case "J"
Base2 = Base2 & "01001"
Case "K"
Base2 = Base2 & "01010"
Case "L"
Base2 = Base2 & "01011"
Case "M"
Base2 = Base2 & "01100"
Case "N"
Base2 = Base2 & "01101"
Case "O"
Base2 = Base2 & "01110"
Case "P"
Base2 = Base2 & "01111"
Case "Q"
Base2 = Base2 & "10000"
Case "R"
Base2 = Base2 & "10001"
Case "S"
Base2 = Base2 & "10010"
Case "T"
Base2 = Base2 & "10011"
Case "U"
Base2 = Base2 & "10100"
Case "V"
Base2 = Base2 & "10101"
Case "W"
Base2 = Base2 & "10110"
Case "X"
Base2 = Base2 & "10111"
Case "Y"
Base2 = Base2 & "11000"
Case "Z"
Base2 = Base2 & "11001"
Case "2"
Base2 = Base2 & "11010"
Case "3"
Base2 = Base2 & "11011"
Case "4"
Base2 = Base2 & "11100"
Case "5"
Base2 = Base2 & "11101"
Case "6"
Base2 = Base2 & "11110"
Case "7"
Base2 = Base2 & "11111"
Case Else
Base32To16 = CVErr(xlErrValue)
Exit Function
End Select
Next i
If Not Len(Base2) Mod 4 = 0 Then
Base2 = String(4 - (Len(Base2) Mod 4), "0") & Base2
End If
'  If Len(Base2) > 4 And Left(Base2, 4) = "0000" Then
'    Base2 = Mid(Base2, 5)
'  End If
Base32To16 = ""
For i = 1 To Len(Base2) Step 4
Select Case Mid(Base2, i, 4)
Case "0000"
Base32To16 = Base32To16 & "0"
Case "0001"
Base32To16 = Base32To16 & "1"
Case "0010"
Base32To16 = Base32To16 & "2"
Case "0011"
Base32To16 = Base32To16 & "3"
Case "0100"
Base32To16 = Base32To16 & "4"
Case "0101"
Base32To16 = Base32To16 & "5"
Case "0110"
Base32To16 = Base32To16 & "6"
Case "0111"
Base32To16 = Base32To16 & "7"
Case "1000"
Base32To16 = Base32To16 & "8"
Case "1001"
Base32To16 = Base32To16 & "9"
Case "1010"
Base32To16 = Base32To16 & "A"
Case "1011"
Base32To16 = Base32To16 & "B"
Case "1100"
Base32To16 = Base32To16 & "C"
Case "1101"
Base32To16 = Base32To16 & "D"
Case "1110"
Base32To16 = Base32To16 & "E"
Case "1111"
Base32To16 = Base32To16 & "F"
End Select
Next i
End Function```

8. The Following User Says Thank You to Maudibe For This Useful Post:

Jimmy-W (2014-09-01)

9. Thanks again, Maudibe. This is great! Question: instead of commenting out the lines, can I just delete them from the module? Also, is there a way to make this module show up in my macro list?

10. Jimmy,

Yes, you can delete them once you are sure that the code acts the way you expect it. However, if you ever need conversions in the future that do not involve hash tags and you want leading zeros ignored, you will need to add back those lines. Either way it is your choice to remove them as they are not needed for what you want to do here.

When you refer to "Macro list", are you referring to the list of macros displayed when you press Alt-F8? The macros will be listed if you placed them in a standard module but not the module name itself. If they were in a sheet module instead (which they should not be), then the module would be listed in front of the macro name

Maud

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•