Results 1 to 8 of 8
Thread: Function to convert number base

20140827, 10:53 #1
 Join Date
 Jan 2001
 Location
 Helena, Montana, USA
 Posts
 201
 Thanks
 10
 Thanked 0 Times in 0 Posts
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 SHA1 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!JimmyW
Helena, MT

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140828, 08:51 #2
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,766
 Thanks
 5
 Thanked 0 Times in 0 Posts
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 base32.
First, if you've entered a base16 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 base16 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 rightmost 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 rightmost 3 digits of 1000+variable.
Not sure if any of this helps but just some thoughts.
Fred

20140828, 11:26 #3
 Join Date
 Jan 2001
 Location
 Helena, Montana, USA
 Posts
 201
 Thanks
 10
 Thanked 0 Times in 0 Posts
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 SHA1 hashes. While Base16 could consist entirely of numbers, it would be quite unusual, if not infeasible. Basically, the SHA1 in hex consists of 20 bytes, or 40 alphanumeric 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.
JimmyW
Helena, MT

20140828, 19:43 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,199
 Thanks
 45
 Thanked 228 Times in 211 Posts
Jimmy,
Excel is not removing the leading zero; your code is. The following lines are your culprit:
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

The Following User Says Thank You to Maudibe For This Useful Post:
JimmyW (20140829)

20140829, 11:53 #5
 Join Date
 Jan 2001
 Location
 Helena, Montana, USA
 Posts
 201
 Thanks
 10
 Thanked 0 Times in 0 Posts
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!JimmyW
Helena, MT

20140829, 16:02 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,199
 Thanks
 45
 Thanked 228 Times in 211 Posts
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

The Following User Says Thank You to Maudibe For This Useful Post:
JimmyW (20140901)

20140901, 17:47 #7
 Join Date
 Jan 2001
 Location
 Helena, Montana, USA
 Posts
 201
 Thanks
 10
 Thanked 0 Times in 0 Posts
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?
JimmyW
Helena, MT

20140901, 18:30 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,199
 Thanks
 45
 Thanked 228 Times in 211 Posts
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 AltF8? 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
MaudLast edited by Maudibe; 20140901 at 18:35.