I would like to use conditional formatting in excel to format all rows with the same value in a given column in the same way (see the example below)

I thought this could be simply done by applying a color-scale to the data, but I would need to convert all the values from text strings into numbers.

What is the best way to approach this?

I tried a few approaches to convert the keys into numbers and then use a color-scale, but Asc, VALUE, and DATEVALUE did not work (Asc works on characters, not full strings, and both values errored on my strings). While I could brute-force compute the summed ASCII value, there’s a potential for duplicates and I assume there’s a better way.