I assume that the "suit" (c,d,h,s) does not matter.
I have been able to separate the one column into 6 columns not including the total.
The next step is to convert K's. Q's, J's to = 10 then remove the "suits" convert the Ace and SHAZAM LOL We got it
Havent used excel in years so will work on it
Well I'm quite good with Excel - but this is a toughie!
I could do it, but I would do it the long (dummies) way - requiring 4 extra columns per card to start with, then a few more at the end to handle the 'Ace' issue:-
(This was done on Excel 97 - hopefully the newer versions are similar!)
First, (assuming the suits are not important!) get rid of all the suit letters & the commas, and have 1 card per column;
Highlight all the cells with the data in, click 'Data', 'Text to columns', 'Fixed width', 'Next'.
Here put a break between each symbol (shown half-done in this screenie):-
Old Attachment (Invalid)
The result is at the top of this next picture.
Remove the unwanted columns, and you'll end up with the bottom part of this picture:-
Old Attachment (Invalid)
For each card (or column), add formulas in 4 more columns to give values of 11 for Aces & 10 for picture cards. I have shown the 'IF' formulas in blue under the cells. (Only shown for column 'A' in my shot).
Do a sum at the end for the entire row starting from column A.
In the columns to the
right of your answer, add the second bit below to account for the Aces.
(Here shown underneath instead):-
Old Attachment (Invalid)
Copy & Paste these formula all the way down your spreadsheet.
A bit long winded - but quite simple I think...?
Errr.... Now you have ended up with the total you had in the first palce... what was the question again?
KK