In Depth

Excelling at Excel number base conversions

When Excel doesn't have a function to do something you have to find a workaround ... it turns out converting between number bases is kinda tricky

Numbers
Credit: Ken Teegarden / Flickr

Every now and then you come across something that Excel doesn’t have a function for which requires you to get fancy and figure out some tricky way to get the job done. While coding up a solution in VBA is always an option, VBA functions make your spreadsheets unsecure for other people and, should you send the spreadsheet to someone who has either disabled VBA or has a different version of Excel, it will break the spreadsheet’s logic.

I recently needed to convert decimal numbers into base 36 in an Excel for Mac 2011 spreadsheet. Now Windows Excel has the Radix function, which converts base 10 numbers into any base between 2 and 36 inclusive, there’s no such beast in Excel for Mac.

I started to develop a formula but in a head-slapping moment of realization started searching the Web and I finally found an answer in a post, Converting from Decimal to Base-36 in Excel Formula in microsoft.public.excel.worksheet.functions, a Google Group. The technique used is clever and with a little tweaking you can make this formula even more useful. First of all, here’s the formula which I've rewritten to make it easier to understand and modified to generalize its use:

=CONCATENATE(

   IF(FLOOR(Value/Radix^8,1)=0,"0",

      IF(MOD(FLOOR(Value/Radix^8,1),Radix)>9,

          CHAR(MOD(FLOOR(Value/Radix^8,1)-10,Radix)+65),

          MOD(FLOOR(Value/Radix^8,1),Radix))

   ),

   IF(FLOOR(Value/Radix^7,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^7,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^7,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^7,1),Radix))

   ),

   IF(FLOOR(Value/Radix^6,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^6,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^6,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^6,1),Radix))

   ),

   IF(FLOOR(Value/Radix^5,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^5,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^5,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^5,1),Radix))

   ),

   IF(FLOOR(Value/Radix^4,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^4,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^4,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^4,1),Radix))

   ),

   IF(FLOOR(Value/Radix^3,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^3,1),Radix)>9,

            CHAR(MOD(FLOOR(Value/Radix^3,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^3,1),Radix))

   ),

   IF(FLOOR(Value/Radix^2,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^2,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^2,1)-10,Radix)+65),

          MOD(FLOOR(Value/Radix^2,1),Radix))

   ),

   IF(FLOOR(Value/Radix^1,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^1,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^1,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^1,1),Radix))

   ),

   IF(MOD(FLOOR(Value/Radix^0,1),Radix)>9,

       CHAR(MOD(FLOOR(Value/Radix^0,1)-10,Radix)+65),

       MOD(FLOOR(Value/Radix^0,1),Radix)

   )

)

Exactly how and why this works is left as an exercise for the reader but I’ll explain some of the features of the formula and the tweaks I’ve added:

  • The formula as above creates an 8 digit number in a base specified by the  cell named Radix in text format from a value in cell the cell named Value. Each digit is created by a section like this (unless its the code for the least significant place you'll need a comma after it):

 IF(FLOOR(Value/Radix^x,1)=0,"0",

       IF(MOD(FLOOR(Value/Radix^x,1),Radix)>9,

           CHAR(MOD(FLOOR(Value/Radix^x,1)-10,Radix)+65),

           MOD(FLOOR(Value/Radix^x,1),Radix))

   )

  • The cell named Value contains the decimal value to be converted and the x’s are replaced with the place number of the digit (place 1 is the rightmost). If you want more digits then add additional sections at the beginning and ensure each section’s place number is sequential.
  • If you don’t want leading zeros or you want to use some other character such as a dash then replace the text “0” with either “” or “-“ respectively.
  • You can set Radix to be any value from 2 to 36 and the output will use the characters 0 to 9 and then A through Z.
  • If you’d prefer to use the lower case alphabet change the +65 to +97. If you use the upper case alphabet and a Radix value 37 up to 201 you’ll wind up using the rest of the ASCII character set from 91 (“[“) to 255(“ˇ”).

If you want to use, for example, base 62 going from, say, 0 to 9, then a to z, then A to Z (or use some other completely different set of symbols) you can use a formula I came up with that’s more flexible and somewhat simpler than the one above. My formula uses a lookup table with as many entries as the desired base. Thus, for base 62 you would add

0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ

… to a cell and name it Digits. Create other named cells for the base (Radix), the leading character symbol (Leading_char), the value to convert (Value), and in a cell enter:

=CONCATENATE(

   IF(FLOOR(Value/Radix^8,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^8,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^7,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^7,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^6,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^6,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^5,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^5,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^4,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^4,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^3,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^3,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^2,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^2,1),Radix)+1,1)),

   IF(FLOOR(Value/Radix^1,1)=0,Leading_char,

       MID(Digits,MOD(FLOOR(Value/Radix^1,1),Radix)+1,1)),

   MID(Digits,MOD(FLOOR(Value/Radix^0,1),Radix)+1,1)

)

Obviously you can replace Value with a cell reference if preferred.

The largest value this formula can convert from base 10 is 40,532,399,999,999 which is, in base 36, ed8bsfzzz.

Finally, should you need to convert from base 36 to base 10, here’s a formula:

=IF(ValueB64="","0",SUMPRODUCT(POWER(36,LEN(ValueB64)-ROW(INDIRECT("1:"&LEN(ValueB64)))), (CODE(UPPER(MID(ValueB64,ROW(INDIRECT("1:"&LEN(ValueB64))),1)))-48*(CODE(MID(ValueB64, ROW(INDIRECT("1:"&LEN(ValueB64))),1))<58)-55*(CODE(MID(ValueB64, ROW(INDIRECT("1:"&LEN(ValueB64))),1))>64))))

If you’ve got any neat Excel formulae that you’d like to share, let me know …

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.
Must read: Hidden Cause of Slow Internet and how to fix it
Notice to our Readers
We're now using social media to take your comments and feedback. Learn more about this here.