Results 1 to 11 of 11
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Is it possible, in Excel 2003 or 2007, to convert a column of base 16 numbers to base 32? The base 16 values are in Column A. I'd like to enter a formula in B1 to convert A1, and simply copy the formula down. Thanks.
    JimmyW
    Helena, MT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use the custom VBA function from Convert From Base To Base Udf - Excel Help & Excel Macro Help.

    The formula in B1 would be

    =Bas2Bas(A1,16,32)

    If you store the function in your Personal.xls workbook, the formula becomes

    =Personal.xls!Bas2Bas(A1,16,32)

  3. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks very much, Hans. I'm afraid that I'm going to need a little help in doing vba. I can get from Tools to the VBA editor, but I'm lost from there. I have done macros, but I've just always recorded them from the keyboard. At this point, I've managed to get into the VBA editor in Personal.xls, where I copied the script from the link you cited.
    JimmyW
    Helena, MT

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you copied the code of the Bas2Bas function into a module in Personal.xls, that's all you need to do in Visual Basic. You should now be able to use the function in formulas in cells just like other Excel functions, e.g.

    =Personal.xls!Bas2Bas(A1,16,32)

    Please note that the function as posted will only work correctly for numbers up to 2^31-1 = 2,147,483,647 (decimal). You can extend its range by declaring dValue as Currency instead of as Long.

  5. #5
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thanks again, Hans! That certainly does work. However, what I want to convert is a base 16, SHA-1 hash value to base 32, and vice versa. Base 16 is D5C35F7C46966EF14BE06D7C5986382EB835587F, and base 32 is 2XBV67CGSZXPCS7ANV6FTBRYF24DKWD7. Perhaps I've chosen to large of a number.
    JimmyW
    Helena, MT

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Those numbers are much larger than Excel can handle, so the conversion routine would have to be written entirely using strings. I'll see if I can come up with something.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If base-32 is written analogously to base-16 (hexadecimal), it uses the digits 0-9 and the letters A-V. In your example W, X, Y and Z are also used. Can you explain how that works?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='785149' date='17-Jul-2009 19:28']If base-32 is written analogously to base-16 (hexadecimal), it uses the digits 0-9 and the letters A-V. In your example W, X, Y and Z are also used. Can you explain how that works?[/quote]
    Does this help Hans?
    Regards
    Don

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='785216' date='18-Jul-2009 23:15']Does this help Hans?[/quote]
    Yes, it does! Thanks a lot!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks to Don Wells, I now know the coding system you're using for base 32.

    The attached text file contains two functions that you can copy into a VBA module: Base16To32 converts a base-16 number (as a string) to a base-32 number (as a string), and Base32To16 does the reverse.

    If you store the function in a module in your Personal.xls macro workbook, you can use the functions like this in a formula in a cell:

    =Personal.xls!Base16To32(A1)

    and

    =Personal.xls!Base32To16(A1)

    The functions can work with very long strings (but they will become rather slow).
    Attached Files Attached Files

  11. #11
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thank you so much, Hans! And thanks to Don, too. Works great. I appreciate your help very much.
    JimmyW
    Helena, MT

Posting Permissions

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