+ Reply to Thread
Results 1 to 4 of 4

Convert Base 10 to any other numeric system

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Convert Base 10 to any other numeric system

    I needed a Base 10 to Base 36 converter and found this very neat formula on Google groups;

    SOURCE

    =CONCATENATE(IF(FLOOR(A1/$D$1^12,1)=0,"",IF(MOD(FLOOR(A1/$D$1^12,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^12,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^12,1),$D$1))),IF(FLOOR(A1/$D$1^11,1)=0,"",IF(MOD(FLOOR(A1/$D$1^11,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^11,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^11,1),$D$1))),IF(FLOOR(A1/$D$1^10,1)=0,"",IF(MOD(FLOOR(A1/$D$1^10,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^10,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^10,1),$D$1))),IF(FLOOR(A1/$D$1^9,1)=0,"",IF(MOD(FLOOR(A1/$D$1^9,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^9,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^9,1),$D$1))),IF(FLOOR(A1/$D$1^8,1)=0,"",IF(MOD(FLOOR(A1/$D$1^8,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^8,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^8,1),$D$1))),IF(FLOOR(A1/$D$1^7,1)=0,"",IF(MOD(FLOOR(A1/$D$1^7,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^7,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^7,1),$D$1))),IF(FLOOR(A1/$D$1^6,1)=0,"",IF(MOD(FLOOR(A1/$D$1^6,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^6,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^6,1),$D$1))),IF(FLOOR(A1/$D$1^5,1)=0,"",IF(MOD(FLOOR(A1/$D$1^5,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^5,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^5,1),$D$1))),IF(FLOOR(A1/$D$1^4,1)=0,"",IF(MOD(FLOOR(A1/$D$1^4,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^4,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^4,1),$D$1))),IF(FLOOR(A1/$D$1^3,1)=0,"",IF(MOD(FLOOR(A1/$D$1^3,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^3,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^3,1),$D$1))),IF(FLOOR(A1/$D$1^2,1)=0,"",IF(MOD(FLOOR(A1/$D$1^2,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^2,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^2,1),$D$1))),IF(FLOOR(A1/$D$1^1,1)=0,"",IF(MOD(FLOOR(A1/$D$1^1,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^1,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^1,1),$D$1))),IF(MOD(FLOOR(A1/$D$1^0,1),$D$1)>9,CHAR(MOD(FLOOR(A1/$D$1^0,1)-10,$D$1)+65),MOD(FLOOR(A1/$D$1^0,1),$D$1)))

    It appears to convert any decimal number in A1 to the value in base (D1)

    So a 255 in A1 and a 16 in D1 gives you the hexadecimal value of 255 = FF
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Convert Base 10 to any other numeric system

    This VBA UDF is much more robust than your formula, used like

    =ConvertBase(A1,D1)

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 10-03-2014 at 01:43 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Convert Base 10 to any other numeric system

    Agreed, the UDF would be the better way to do it, but not all users can use a VBA solution, and a formula is easier to plug & play for less experienced Excel users.

    Also, code tags!

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Convert Base 10 to any other numeric system

    Another VBA solution, same source as the original post:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to convert numeric number value to numeric years months
    By sonu_kumar444 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-16-2014, 03:06 AM
  2. Different number base system
    By evertjvr in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-23-2012, 05:37 AM
  3. convert non data base to data base format using formula
    By murarihyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-22-2011, 11:42 AM
  4. Numeric base conversion
    By tonywig in forum Excel General
    Replies: 5
    Last Post: 10-10-2007, 11:38 AM
  5. Replies: 4
    Last Post: 02-06-2006, 03:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1