Results 1 to 11 of 11
Thread: Formula to convert number base

20090715, 16:43 #1
 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

20090715, 16:54 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20090717, 12:04 #3
 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

20090717, 12:55 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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^311 = 2,147,483,647 (decimal). You can extend its range by declaring dValue as Currency instead of as Long.

20090717, 14:03 #5
 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, SHA1 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

20090717, 14:34 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20090717, 18:28 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
If base32 is written analogously to base16 (hexadecimal), it uses the digits 09 and the letters AV. In your example W, X, Y and Z are also used. Can you explain how that works?

20090718, 16:15 #8
 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='17Jul2009 19:28']If base32 is written analogously to base16 (hexadecimal), it uses the digits 09 and the letters AV. In your example W, X, Y and Z are also used. Can you explain how that works?[/quote]
Does this help Hans?Regards
Don

20090718, 17:16 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
[quote name='wdwells' post='785216' date='18Jul2009 23:15']Does this help Hans?[/quote]
Yes, it does! Thanks a lot!

20090718, 17:22 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 base16 number (as a string) to a base32 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).

20090721, 13:44 #11
 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