Results 1 to 8 of 8
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    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 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!
    JimmyW
    Helena, MT

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,765
    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 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

  4. #3
    3 Star Lounger Jimmy-W's Avatar
    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 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.
    JimmyW
    Helena, MT

  5. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,116
    Thanks
    39
    Thanked 201 Times in 188 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

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

    Jimmy-W (2014-08-29)

  7. #5
    3 Star Lounger Jimmy-W's Avatar
    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

  8. #6
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,116
    Thanks
    39
    Thanked 201 Times in 188 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

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

    Jimmy-W (2014-09-01)

  10. #7
    3 Star Lounger Jimmy-W's Avatar
    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

  11. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,116
    Thanks
    39
    Thanked 201 Times in 188 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 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
    Last edited by Maudibe; 2014-09-01 at 18:35.

Posting Permissions

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