i have this : 2S 5D [AC KH 2S QS JS] in cell b2 for Google Sheets

[dabielcrbyton]

a	b	c	d	e
a1
a2	2S 5D [AC KH 2S QS JS]	2 5 [A K 2 Q J]	2 5	[2 J Q K A ]
a3

please MY FRIENDS

i have this : 2S 5D [AC KH 2S QS JS]
in cell b2 ( it is the cards i have in poker)

i need function in c2 that will do that :
2 5 [A K 2 Q J]
(delete the 4 shape of every card in poker S,D,H,C)



also i need in d2 function that will do that :
2 5
it will set the cards by order from the low card
to the highest cards in poker
by order before the Parenthesis [ ]
FROM in c2


also i need in e2 function that will do that :
[2 J Q K A ]
it will set the cards by order from the low card
to the highest cards in poker
by order inside the Parenthesis [ ]
FROM in c2

the order on card in poker is :
A : IS THE highest CARD
K
q
J
10
9
8
7
6
5
4
3
2 : IS THE LOWEST CARD

 

[JGordon11]

If your version of Excel has TEXTJOIN function the following formulas should work.

PQ Date Transform.xlsm
	A	B	C	D	E
1
2		10S 2D [AC KH 10S QS JS]	10 2 [A K 10 Q J]	2 10	[10 J Q K A]
Sheet6

Cell Formulas
Range	Formula
C2	C2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"C",""),"D",""),"H",""),"S","")
D2	D2	=TEXTJOIN(" ",1,SUBSTITUTE(MID("23456789XJQKA",SMALL(FIND(MID(SUBSTITUTE(C2,"10","X"),{1,3},1),"23456789XJQKA"),{1,2}),1),"X","10"))
E2	E2	="[" & SUBSTITUTE(TEXTJOIN(" ",1,MID("23456789XJQKA",SMALL(FIND(MID(SUBSTITUTE(MID(C2,FIND("[",C2),20),"10","X"),{2,4,6,8,10},1),"23456789XJQKA"),{1,2,3,4,5}),1)),"X","10") & "]"

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Could we have another 4 or 5 varied sample rows and the expected results?

 

[Peter_SSs]

If your version of Excel has TEXTJOIN function the following formulas should work.

I have asked for more sample data to confirm if it is possible, but if the sections can vary in size I think your formulas will fail.

Assuming Microsoft 365 or Excel 2019 is available, this is my suggestion.

21 08 03.xlsm
	A	B	C	D	E
1
2		2S 5D [AC KH 2S QS JS]	2 5 [A K 2 Q J]	2 5	[2 J Q K A]
3		2S KC 5D [AC KH QS JS]	2 K 5 [A K Q J]	2 5 K	[J Q K A]
4		2S KC QD [AC 9H 4C JS]	2 K Q [A 9 4 J]	2 Q K	[4 9 J A]
5		2S 10C 5D [AC KH QS JS]	2 10 5 [A K Q J]	2 5 10	[J Q K A]
6		5D [KS 3D AC JD 3H JC]	5 [K 3 A J 3 J]	5	[3 3 J J K A]
Sort Cards

Cell Formulas
Range	Formula
C2:C6	C2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"C",""),"D",""),"H",""),"S","")
D2:D6	D2	=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",1,SORT(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(C2,FIND("[",C2)-2),"A","ZA"),"K","YK")," ","</c><c>")&"</c></p>","//c"))),"Z",""),"Y","")
E2:E6	E2	="["&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" ",1,SORT(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(LEFT(C2,LEN(C2)-1),1,FIND("[",C2),""),"A","ZA"),"K","YK")," ","</c><c>")&"</c></p>","//c"))),"Z",""),"Y","")&"]"

 

[JGordon11]

It looks like it might be modelling Texas Holdem which would always be two personal cards and 5 shared cards. If that's the case I think my formulas should work

 

[dabielcrbyton]

If your version of Excel has TEXTJOIN function the following formulas should work.

PQ Date Transform.xlsm
	A	B	C	D	E
1
2		10S 2D [AC KH 10S QS JS]	10 2 [A K 10 Q J]	2 10	[10 J Q K A]
Sheet6

Cell Formulas
Range	Formula
C2	C2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"C",""),"D",""),"H",""),"S","")
D2	D2	=TEXTJOIN(" ",1,SUBSTITUTE(MID("23456789XJQKA",SMALL(FIND(MID(SUBSTITUTE(C2,"10","X"),{1,3},1),"23456789XJQKA"),{1,2}),1),"X","10"))
E2	E2	="[" & SUBSTITUTE(TEXTJOIN(" ",1,MID("23456789XJQKA",SMALL(FIND(MID(SUBSTITUTE(MID(C2,FIND("[",C2),20),"10","X"),{2,4,6,8,10},1),"23456789XJQKA"),{1,2,3,4,5}),1)),"X","10") & "]"

no
my friend
i use google doc
i the C the function work amazing
but in the D
and in E
the function do not work



can you help me fix it
please

 

[Fluff]

i use google doc

When using anything other than Excel, you should clearly state that in your opening post & you should also post the question in the General Discussion & Other Applications section of the board. I have moved it there for you this time.

 

[dabielcrbyton]

When using anything other than Excel, you should clearly state that in your opening post & you should also post the question in the General Discussion & Other Applications section of the board. I have moved it there for you this time.

OK
i post it
in the General Discussion & Other Applications

under the title :

google sheet (delete the 4 shape of every card in poker S,D,H,C)​

i am sorry
it been long time
i used mrexcel in the past

 

[Fluff]

I have deleted your new thread as it is a duplicate of this one. I told you that I had moved the thread for you.

 

[Peter_SSs]

It looks like it might be modelling Texas Holdem which would always be two personal cards and 5 shared cards.

You may be right. Shows how much I know about poker! ?