Discussions

View Only

Convert Base 10 to base 36

• 1.  Convert Base 10 to base 36

Posted 06-26-2017 00:12
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      2S2000     1JK  5000     3UV  20,000   FFK  25,000   JAG  50,000   12KW   100,000  255S    `

• 2.  RE: Convert Base 10 to base 36

Top
Contributor
Posted 06-26-2017 02:42
Not with a formula, but with a code page yes.

• 3.  RE: Convert Base 10 to base 36

Posted 06-26-2017 15:15
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))
)

• 4.  RE: Convert Base 10 to base 36

Top
Contributor
Posted 06-26-2017 16:18
>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

• 5.  RE: Convert Base 10 to base 36

Posted 06-26-2017 19:56
Dan,

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