# Thread: Formula to convert number base

1. 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.

2. 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. 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.

4. 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. 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.

6. 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. 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. [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?

9. [quote name='wdwells' post='785216' date='18-Jul-2009 23:15']Does this help Hans?[/quote]
Yes, it does! Thanks a lot!

10. 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).

11. Thank you so much, Hans! And thanks to Don, too. Works great. I appreciate your help very much.

#### Posting Permissions

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