Convert Base 10 to base 36

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Is there a way in QuickBase to convert a number from base 10 to base 36 using a formula?
For example:

Base 10  Base 36
100      2S
2000 1JK 5000 3UV 20,000 FFK 25,000 JAG 50,000 12KW 100,000 255S    
Photo of JimLieder

JimLieder

  • 188 Points 100 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Not with a formula, but with a code page yes.
Photo of JimLieder

JimLieder

  • 188 Points 100 badge 2x thumb
Found this Excel formula.  Not sure if it's possible to use this to Excel formula as a model for a QuickBase formula, but I'll give it go.

A2 contains the Base 10 number
B2 contains the base number conversion (base 2 to 36)
C2 contains this formula:

=CONCATENATE(
IF(FLOOR(A2/$B$2^12,1)=0,"",IF(MOD(FLOOR(A2/$B$2^12,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^12,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^12,1),$B$2))),
IF(FLOOR(A2/$B$2^11,1)=0,"",IF(MOD(FLOOR(A2/$B$2^11,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^11,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^11,1),$B$2))),
IF(FLOOR(A2/$B$2^10,1)=0,"",IF(MOD(FLOOR(A2/$B$2^10,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^10,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^10,1),$B$2))),
IF(FLOOR(A2/$B$2^9,1)=0,"",IF(MOD(FLOOR(A2/$B$2^9,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^9,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^9,1),$B$2))),
IF(FLOOR(A2/$B$2^8,1)=0,"",IF(MOD(FLOOR(A2/$B$2^8,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^8,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^8,1),$B$2))),
IF(FLOOR(A2/$B$2^7,1)=0,"",IF(MOD(FLOOR(A2/$B$2^7,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^7,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^7,1),$B$2))),
IF(FLOOR(A2/$B$2^6,1)=0,"",IF(MOD(FLOOR(A2/$B$2^6,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^6,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^6,1),$B$2))),
IF(FLOOR(A2/$B$2^5,1)=0,"",IF(MOD(FLOOR(A2/$B$2^5,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^5,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^5,1),$B$2))),
IF(FLOOR(A2/$B$2^4,1)=0,"",IF(MOD(FLOOR(A2/$B$2^4,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^4,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^4,1),$B$2))),
IF(FLOOR(A2/$B$2^3,1)=0,"",IF(MOD(FLOOR(A2/$B$2^3,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^3,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^3,1),$B$2))),
IF(FLOOR(A2/$B$2^2,1)=0,"",IF(MOD(FLOOR(A2/$B$2^2,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^2,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^2,1),$B$2))),
IF(FLOOR(A2/$B$2^1,1)=0,"",IF(MOD(FLOOR(A2/$B$2^1,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^1,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^1,1),$B$2))),
IF(MOD(FLOOR(A2/$B$2^0,1),$B$2)>9,CHAR(MOD(FLOOR(A2/$B$2^0,1)-10,$B$2)+65),MOD(FLOOR(A2/$B$2^0,1),$B$2))
)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,532 Points 20k badge 2x thumb
>Not with a formula, but with a code page yes.

This is easy to do with native formulas. You will have to extend the pattern of the formulas to convert decimal numbers that are larger than 36^4 - 1 = 1,679,615.

Feel free to add you own records to test:

Convert Bases ~ List All
https://haversineconsulting.quickbase.com/db/bmvutfx28?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=587

Base 10 to Base 36 Conversion Utility
http://www.unitconversion.org/numbers/base-10-to-base-36-conversion.html
Photo of JimLieder

JimLieder

  • 188 Points 100 badge 2x thumb
Dan, 

This is slick.  I've added several new records and it' calculating the Base 36 # correctly.  GREAT!