Convert RGB to Hex (decimal to hexadecimal)

Fabian_Weller · November 15, 2019, 8:06pm

Here is an expression to convert a RGB decimal number to a Hexadecimal.
I used it in a color mixer.

The column [Hex #] is a TEXT column with the initial value:

CONCATENATE(
SWITCH(Floor([Red]/16),10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",Floor([Red]/16)),
	SWITCH([Red]-Floor([Red]/16)*16,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",[Red]-Floor([Red]/16)*16),

SWITCH(Floor([Green]/16),10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",Floor([Green]/16)),
	SWITCH([Green]-Floor([Green]/16)*16,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",[Green]-Floor([Green]/16)*16),

  SWITCH(Floor([Blue]/16),10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",Floor([Blue]/16)),
	SWITCH([Blue]-Floor([Blue]/16)*16,10,"A",11,"B",12,"C",13,"D",14,"E",15,"F",[Blue]-Floor([Blue]/16)*16)
)

GreenFlux · November 15, 2019, 8:26pm

Awesome! Very creative use of the sliders.

QREW_Cam · November 15, 2019, 8:41pm

I love this so much! This community is pushing AppSheet further and further in such creative directions!

Grant_Stead · November 15, 2019, 10:38pm

I was about to ask the other day… But didn’t lol

anonymous · May 9, 2023, 10:03am

Thanks for this !

I improved the system to choose between Hex mode and RGB mode :

Here are the inverse expressions, allowing to obtain the RGB values from a hexadecimal code :

Red :

SWITCH(
 LEFT([HexEdit],2),
 "00",0,"01",1,"02",2,"03",3,"04",4,"05",5,"06",6,"07",7,"08",8,"09",9,"0A",
 10,"0B",11,"0C",12,"0D",13,"0E",14,"0F",15,"10",16,"11",17,"12",18,"13",19,"14",
 20,"15",21,"16",22,"17",23,"18",24,"19",25,"1A",26,"1B",27,"1C",28,"1D",29,"1E",
 30,"1F",31,"20",32,"21",33,"22",34,"23",35,"24",36,"25",37,"26",38,"27",39,"28",
 40,"29",41,"2A",42,"2B",43,"2C",44,"2D",45,"2E",46,"2F",47,"30",48,"31",49,"32",
 50,"33",51,"34",52,"35",53,"36",54,"37",55,"38",56,"39",57,"3A",58,"3B",59,"3C",
 60,"3D",61,"3E",62,"3F",63,"40",64,"41",65,"42",66,"43",67,"44",68,"45",69,"46",
 70,"47",71,"48",72,"49",73,"4A",74,"4B",75,"4C",76,"4D",77,"4E",78,"4F",79,"50",
 80,"51",81,"52",82,"53",83,"54",84,"55",85,"56",86,"57",87,"58",88,"59",89,"5A",
 90,"5B",91,"5C",92,"5D",93,"5E",94,"5F",95,"60",96,"61",97,"62",98,"63",99,"64",
 100,"65",101,"66",102,"67",103,"68",104,"69",105,"6A",106,"6B",107,"6C",108,"6D",109,"6E",
 110,"6F",111,"70",112,"71",113,"72",114,"73",115,"74",116,"75",117,"76",118,"77",119,"78",
 120,"79",121,"7A",122,"7B",123,"7C",124,"7D",125,"7E",126,"7F",127,"80",128,"81",129,"82",
 130,"83",131,"84",132,"85",133,"86",134,"87",135,"88",136,"89",137,"8A",138,"8B",139,"8C",
 140,"8D",141,"8E",142,"8F",143,"90",144,"91",145,"92",146,"93",147,"94",148,"95",149,"96",
 150,"97",151,"98",152,"99",153,"9A",154,"9B",155,"9C",156,"9D",157,"9E",158,"9F",159,"A0",
 160,"A1",161,"A2",162,"A3",163,"A4",164,"A5",165,"A6",166,"A7",167,"A8",168,"A9",169,"AA",
 170,"AB",171,"AC",172,"AD",173,"AE",174,"AF",175,"B0",176,"B1",177,"B2",178,"B3",179,"B4",
 180,"B5",181,"B6",182,"B7",183,"B8",184,"B9",185,"BA",186,"BB",187,"BC",188,"BD",189,"BE",
 190,"BF",191,"C0",192,"C1",193,"C2",194,"C3",195,"C4",196,"C5",197,"C6",198,"C7",199,"C8",
 200,"C9",201,"CA",202,"CB",203,"CC",204,"CD",205,"CE",206,"CF",207,"D0",208,"D1",209,"D2",
 210,"D3",211,"D4",212,"D5",213,"D6",214,"D7",215,"D8",216,"D9",217,"DA",218,"DB",219,"DC",
 220,"DD",221,"DE",222,"DF",223,"E0",224,"E1",225,"E2",226,"E3",227,"E4",228,"E5",229,"E6",
 230,"E7",231,"E8",232,"E9",233,"EA",234,"EB",235,"EC",236,"ED",237,"EE",238,"EF",239,"F0",
 240,"F1",241,"F2",242,"F3",243,"F4",244,"F5",245,"F6",246,"F7",247,"F8",248,"F9",249,"FA",
 250,"FB",251,"FC",252,"FD",253,"FE",254,"FF",255,
 ""
 )

For Green, replace the second line of code with :

MID([HexEdit],3,2),

For Blue, replace the second line of code with :

RIGHT([HexEdit],2),

Suvrutt_Gurjar · May 9, 2023, 12:09pm

Nice one.

As an alternative, the following expression will also convert the 6 digit Hex to RGB. It assumes Hex input in the format “FFFFFF”, or “AABBCC” or “12CDF5” format in the column[Hex_Number] which means hex code without the leading #. It can be of course suitably modified to accommodate and ignore the leading #

CONCATENATE(
(IF(IN(LEFT([Hex_Number],1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), 16*NUMBER(LEFT([Hex_Number],1)),
SWITCH(LEFT([Hex_Number],1), “A”, 160, “B”, 176, “C”, 192, “D”, 208, “E” , 224, “F”, 240,0)
) +

IF(IN(MID([Hex_Number],2,1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), NUMBER(MID([Hex_Number],2,1)),
SWITCH(MID([Hex_Number],2,1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,0)
)),
" ",

(IF(IN(MID([Hex_Number],3,1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), 16*NUMBER(MID([Hex_Number],3,1)),
SWITCH(MID([Hex_Number],3,1), “A”, 160, “B”, 176, “C”, 192, “D”, 208, “E” , 224, “FE”, 240,0)
) +

IF(IN(MID([Hex_Number],4,1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), NUMBER(MID([Hex_Number],4,1)),
SWITCH(MID([Hex_Number],4,1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,0)
)),
" ",

(IF(IN(MID([Hex_Number],5,1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), 16*NUMBER(MID([Hex_Number],5,1)),
SWITCH(MID([Hex_Number],5,1), “A”, 160, “B”, 176, “C”, 192, “D”, 208, “E” , 224, “F”, 240,0)
)+

IF(IN(MID([Hex_Number],6,1), { 1 ,2, 3, 4, 5, 6 , 7, 8, 9}), NUMBER(MID([Hex_Number],6,1)),
SWITCH(MID([Hex_Number],6,1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,0)
))

)

Edit: The following is a shorter expression for 6 digit HEX (000000 to FFFFFF) to RGB conversion. Does not consider leading # .[HEx_Number] is the column for Hex input.

CONCATENATE(
16*(SWITCH(LEFT([Hex_Number],1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(LEFT([Hex_Number],1))))+
SWITCH(MID([Hex_Number],2, 1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(MID([Hex_Number],2, 1))), " ",

16*(SWITCH(MID([Hex_Number],3,1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(MID([Hex_Number],3,1))))+
SWITCH(MID([Hex_Number],4, 1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(MID([Hex_Number],4, 1)))," ",

16*(SWITCH(MID([Hex_Number],5,1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(MID([Hex_Number],5,1))))+
SWITCH(MID([Hex_Number],6, 1), “A”, 10, “B”, 11, “C”, 12, “D”, 13, “E” , 14, “F”, 15,
NUMBER(MID([Hex_Number],6, 1)))

)

anonymous · May 10, 2023, 3:32pm

Now, here are the formulas to convert RGB to HSL (Hue, Saturation, Luminance), which can achieve better color sorting.

Hue :

IF(
  AND([Red] = [Green], [Green] = [Blue]),
  0,
  IF(
    AND(
      [Red] >= [Green],
      [Red] >= [Blue]
    ),
    MOD(60 * ( ([Green] - [Blue]) / ([Red] - [Blue]) ) + 360, 360),
    IF(
      AND(
        [Green] >= [Red],
        [Green] >= [Blue]
      ),
      MOD(60 * (2 + ([Blue] - [Red]) / ([Green] - [Red])) + 360, 360),
      MOD(60 * (4 + ([Red] - [Green]) / ([Blue] - [Green])) + 360, 360)
    )
  )
)

Luminance :

ROUND(
 (((MIN(LIST([Red]/255,[Green]/255,[Blue]/255))
 +
 MAX(LIST([Red]/255,[Green]/255,[Blue]/255)))
 /2)
 *100)
)

Saturation :

IFS(
 MIN(LIST([Red]/255,[Green]/255,[Blue]/255))=MAX(LIST([Red]/255,[Green]/255,[Blue]/255)),
 0,
 
 [Luminance]<=50,
 (MAX(LIST([Red]/255,[Green]/255,[Blue]/255))-MIN(LIST([Red]/255,[Green]/255,[Blue]/255)))
 /
 (MAX(LIST([Red]/255,[Green]/255,[Blue]/255))+MIN(LIST([Red]/255,[Green]/255,[Blue]/255))),
 
 [Luminance]>50,
 (MAX(LIST([Red]/255,[Green]/255,[Blue]/255))-MIN(LIST([Red]/255,[Green]/255,[Blue]/255)))
 /
 (2.0-MAX(LIST([Red]/255,[Green]/255,[Blue]/255))-MIN(LIST([Red]/255,[Green]/255,[Blue]/255)))
 
)*100

Suvrutt_Gurjar · May 10, 2023, 3:37pm

Wow ! @Bulubulu Nice additions to the color code conversion formulas repository. Thank you very much.

anonymous · May 11, 2023, 8:09am

You are welcome ! I’m currently testing, so I don’t guarantee that the code is error free.

This article helped me a lot and made it pretty easy:
https://www.niwa.nu/2013/05/math-behind-colorspace-conversions-rgb-hsl/

This can help if someone wants to do the reverse conversion (HSL to RGB).

Note that I haven’t found the perfect way to sort colors yet. I checked this article for this:
https://www.alanzuccini.com/2015/09/30/colour-sorting/

I did this sorting:

And I get this with some random colors:

Not too bad, but far from perfect. I would like to get something like this:

Or like this :

dbaum · May 11, 2023, 10:58pm

In case it’s helpful to anyone, here are some additional resources that would be possible to incorporate:

Confirm accessibility for low vision users via WebAIM: Contrast Checker’s Contrast Checker API (e.g., https://webaim.org/resources/contrastchecker/?fcolor=0000FF&bcolor=FFFFFF&api))
Use The Color API to retrieve HTML color names or generate color schemes

The relatively basic JSON returned via the URL queries could be sufficiently usable for app developers to just reference directly. Of course, it would also be possible to parse the JSON via Apps Script.

anonymous · May 12, 2023, 1:09pm

RGB to CMYK :

[Key] :

ROUND((1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255))) * 100)

[Cyan] :

IF(
  AND([Red] = 0, [Green] = 0, [Blue] = 0),
  0,
  IF(
    [Key] = 100,
    0,
    ROUND(
      (1 - [Red]/255 - (1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255))))
      * 100/100 / (1 - (1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255))))
      * 100/100 * 100
    )
  )
)

[Magenta] :

IF(
  AND([Red] = 0, [Green] = 0, [Blue] = 0),
  0,
  IF(
    [Key] = 100,
    0,
    ROUND(
      (
        (1 - [Green]/255 - (((1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255)))) * 100)/100)
        / (1 - (((1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255)))) * 100)/100)
      )
      * 100
    )
  )
)

[Yellow] :

IF(
  AND([Red] = 0, [Green] = 0, [Blue] = 0),
  0,
  IF(
    [Key] = 100,
    0,
    ROUND(
      (1 - [Blue]/255 - (((1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255)))) * 100)/100)
      / (1 - (((1 - MAX(LIST([Red]/255, [Green]/255, [Blue]/255)))) * 100)/100)
      * 100
    )
  )
)

anonymous · May 12, 2023, 10:49pm

I used this code to get the color name. It worked fine yesterday, but today it doesn’t work any more. I checked everything, but I dont understand what happen. I continue to investigate.

function getColorName(hexCode) {
  var apiUrl = "https://www.thecolorapi.com/id?hex=" + hexCode;
  
  var response = UrlFetchApp.fetch(apiUrl);
  var data = JSON.parse(response.getContentText());
  
  var colorName = data.name.value;
  return colorName;
}

getColorName()

Topic		Replies	Views
HEX Color Picker - Sample App Tips & Tricks ux , templates	1	47	June 7, 2020
Decimal to base 36 Tips & Tricks expressions	0	18	January 23, 2024
Transparency / Opacity in Format Rules Tips & Tricks ux	23	65	December 28, 2022

Convert RGB to Hex (decimal to hexadecimal)

AI Suggested topics