Looking convert Number/Letter to a Number Only

[kisinana]

Hello to all and wishing you all the best in the New Year

I am looking to solve a pet project.
I want to assign each card from the deck a number ie 1c=1, 1d=2, 1h=3 etc through the whole deck to Ks=52
I have looked at using substitute but it would mean doing all 52 cards and numbers for each cell I want to put the formula.
Is there a way to shorten that up?
I have tried enter the card value in one cell and move and convert the value to another cell
I am not sure if a macro would work? That means after the last card is added I run the macro and all cells convert to a number

Thanks in advance
Kisinana

[June7]

You "have tried" how?
Sure, VBA can do this.
Occurred to me to use ASCII values of characters to calculate a ranking but unfortunately Q has a higher ASCII code than K.
Could build a lookup table and use VLookup() function.

[kisinana]

Thanks June7
What I meant was I looked at using the functions such as "Substitute" or "If" in the cells which allowed me to swap say Ace of Clubs is converted to 1 or King of Diamonds is converted to 50 but not any card and then its numerical value.
Since I just look at this as a hobby I will look at VLookup and play with it.
Thanks for the input.

[June7]

Functions could be used but I expect they would be long and cumbersome.

How do you intend to use this conversion?

Joker not used?

[kisinana]

No at any given time there are normally about 45 players and we just decided cards worked well to draw random teams of four. Keeps everything fair and no separate little cliques form. I just thought I would take it a bit further in case the numbers increase at sometime. Then to print a list later numbers work better.

[June7]

Here is an expression that will calculate number, not as bad as I expected:

=IF(CODE(LEFT(A1))<65,LEFT(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]

I'm not sure I've got the sequence of numbers correctly but try:

=IFERROR(LEFT(A2,LEN(A2)-1)-1,INDEX({1;11;12;13},MATCH(LEFT(A2),{"A";"J";"Q";"K"},0))-1)*4+MATCH(RIGHT(A2),{"c","d","h","s"},0)

or if you've got LET then:

=LET(a,TRIM(A2),IFERROR(LEFT(a,LEN(a)-1)-1,INDEX({1;11;12;13},MATCH(LEFT(a),{"A";"J";"Q";"K"},0))-1)*4+MATCH(RIGHT(a),{"c","d","h","s"},0))

(it's only got one cell reference).
It takes care of the likes of Jh as well as 11h, As as well as 1s etc.

[kisinana]

Hi June7
Thanks very much for that. I tried running it and it sorted by suit first and then went Ace, 10,2,3,4 etc.

Hi p45cal
Thank you both of those worked well. I ran both of those samples and they both begin at 1 and sort first by Aces then 2,3,4 through 10,J,Q,K.

I am able to understand the code from June7 better, but I will now sit down and work through the samples from p45cal. This is the part I enjoy most when toying with formulas getting a better understanding.

Thank you both for your help
and best wishes for you and yours in the New Year.

Kisinana

[June7]

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.

[June7]

And I like LET():

=LET(x,A1,IF(CODE(LEFT(x))<65,LEFT(x,LEN(x)-1),SWITCH(LEFT(x), "A",1,"J",11,"Q",12,"K",13))+SWITCH(RIGHT(x),"c",0,"d",13,"h",26,"s",39))