assigning one of three values to cell to get a score of the row

[mybossmademecomehere]

I am working on making some updates to a training tracker and am having trouble pulling out just the numbers to make adding a score to responses automatic. The possible answers we can get back range from 1-5 and have a letter with it. There can be multiple answers provided for each column. When there are multiple answers, the highest number is what is rated. Each column has a specific range of answers that it can be to either fall into "correct" "buffer" or "deviation." For scoring I want to set the values for correct to 1, buffer to .5, and deviation to 0. I have conditional formatting added right now for manual scoring that changes deviation to red, buffer to blue, and no changes to correct.

I tried this function: =IFS(AND(C2 = "1",C2 <> "2",C2 <> "3", C2 <> "4",C2 <> "5"), 0, C2 = "5", 0, AND(C2 ="2",C2 <> "3",C2 <> "4",C2 <> "5"), 0.5, AND(C2 = "4",C2 <> "5"), 0.5, AND(C2 = "3",C2 <> "4",C2 <> "5"),1). but get #N/A. Because I know if I can get C to work, I haven't went through the process of writing out the equations for D-L

For column C the criteria for each is:
correct:
highest number is equal to 3, there can be 1 - 2, but no 4 - 5

Buffer:
highest number is 2, there can be 1, but no 3-5
or
highest number is 4, there can be 1-3, but no 5

deviation:
highest number is 1, there is no 2-5
or highest number is 5, there can be 1-4

I appreciate any help I can get on this!

 

[dreid1011]

Give this a try:

=LET(
a,C2,
b,MID(a,SEQUENCE(LEN(a)),1),
c,IF((CODE(b)>=48)*(CODE(b)<=57),b,"|"),
d,TEXTSPLIT(CONCAT(c),,"|",TRUE),
e,MAX(VALUE(d)),
IF(e=3,1,IF(OR(e=2,e=4),0.5,0)))

Formula found here: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwi91tOqj-OHAxWLFDQIHXBlB_4QFnoECEcQAQ&url=https://www.reddit.com/r/excel/comments/10mv01h/find_the_max_numerical_value_in_a_string/&usg=AOvVaw2CBlemA_ljwX_1_Z5yDhmM&opi=89978449

I added the IF at the end.