Oops, I forgot about 10 in my Left() function. This version numbers according to your description:
=IF(CODE(LEFT(A1))<65,LEFT(A1,LEN(A1)-1),SWITCH(LEFT(A1), "A",1,"J",11,"Q",12,"K",13))+SWITCH(RIGHT(A1),"c",0,"d",13,"h",26,"s",39)
p45cal solutions use array functions (indicated by the {}) - which I was avoiding. These are tricky to build in older versions of Excel (I have Office2021 but I was still avoiding). Don't think these rank the cards as you described but may be better. Depends if 2s should have higher rank than Kc.