Convert RGB to Hex (decimal to hexadecimal)

Fabian_Weller · 11-15-2019 12:06 PM

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

Awesome! Very creative use of the sliders.

QREW_Cam

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

Grant_Stead

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

Bulubulu

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

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)))

)

Bulubulu

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

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

Bulubulu

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

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.

Bulubulu

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()

Bulubulu

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
    )
  )
)