Extract Specific Text from String

[Ian McPherson]

The below image shows text strings in Col A, and the desired extracted text in col D.
The pattern is shown in bold font and is similar for all strings. All strings are prefixed by '36' except for Mon and Tues.
Thanking you in advance.

 

[KOKOSEK]

That should do the job:

Book1
	A	B
2	silver gross winner (stroke)	36 GROSS STROKE
3	silver Tuesday nett winner (stroke)	TUE NETT STROKE
4	bronze Monday nett winner (sford)	MON NETT SFORD
5	silver senior gross winner (stroke)	36 GROSS STROKE
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LET(before,{"MON";"TUE"},NG,{"NETT";"GROSS"},SS,{"STROKE";"SFORD"},IFERROR(INDEX(before,MATCH(1,COUNTIF($A2,"*"&before&"*"),0)),36)&" "&INDEX(NG,MATCH(1,COUNTIF($A2,"*"&NG&"*"),0))&" "&INDEX(SS,MATCH(1,COUNTIF($A2,"*"&SS&"*"),0)))

 

[awoohaw]

this is also an option:

Book1
	A	B	C	D
1
2	SILVER GROSS WINNER(Stroke)			36 Gross Stroke
3	SILVER GROSS R/U(Stroke)			36 Gross Stroke
4	SILVER NETT WINNER (Stroke)			36 Nett Stroke
5	SILVER NETT R/U (Stroke)			36 Nett Stroke
6	SILVER SENIOR GROSS WINNER (Stroke)			36 Gross Stroke
7	SILVER SENIOR NETT WINNER (Stroke)			36 Nett Stroke
8	SILVER TUESDAY NETT WINNER (Stroke)			TUE Nett Stroke
9	SILVER MONDAY NETT WINNER (Stroke)			MON Nett Stroke
10	BRONZE NETT WINNER (Sford)			36 Nett Sford
11	BRONZE NETT R/U (Sford)			36 Nett Sford
12	BRONZE GROSS WINNER (Sford)			36 Gross Sford
13	BRONZE GROSS R/U (Sford)			36 Gross Sford
14	BRONZE SENIOR GROSS WINNER (Sford)			36 Gross Sford
15	BRONZE SENIOR NET WINNER (Sford)			36 Nett Sford
16	BRONZE TUESDAY NETT WINNER (Sford)			TUE Nett Sford
17	BRONZE MONDAY NETT WINNER(Sford)			MON Nett Sford
Sheet2

Cell Formulas
Range	Formula
D2:D17	D2	=LET(theday,MID($A2,8,3),first,IF(OR(theday="TUE",theday="MON"),theday,"36"), middle,IF(ISNUMBER(SEARCH("GROSS",$A2))," Gross "," Nett "),last,TEXTBEFORE(TEXTAFTER($A2,"(",),")"),first&middle&last)

 

[kvsrinivasamurthy]

Try this macro code.
Data range= Range("A2:A" & Lr)

Sub PickBold()
Dim T&, Lr&, temp, Rng As Range, cel As Range, st$

Lr = Range("A" & Rows.Count).End(xlUp).Row

Set Rng = Range("A2:A" & Lr)
Rng.Offset(0, 1).Clear
For Each cel In Rng
st = "": temp = ""
If InStr(1, cel, "MON") = 0 And InStr(1, cel, "TUE") = 0 Then st = "36 " Else st = ""
    For T = 1 To Len(cel)
    If (cel.Characters(T, 1).Font.Bold = True And Mid(cel, T, 1) <> "(" And Mid(cel, T, 1) <> ")") Or Mid(cel, T, 1) = " " Then
    temp = temp & Mid(cel, T, 1)
    End If
Next T
cel.Offset(0, 1) = st & Trim(temp)

Next cel
End Sub

 

[Ian McPherson]

Thank you all - 3 different but accurate solutions.
Cheers
PS I would have used xl2bb to post but the 'Capture Range' always remains invisible.

 

[Peter_SSs]

PS I would have used xl2bb to post but the 'Capture Range' always remains invisible.

Check the suggestions in this thread.

 

[Ian McPherson]

Thank you all - 3 different but accurate solutions.
Cheers
PS I would have used xl2bb to post but the 'Capture Range' always remains invisible.

Check the suggestions in this thread.

Working now, thanks for the thread.

 

[Peter_SSs]

Working now, thanks for the thread.

Cheers, glad you got it going.

A couple of other formula options.
Col D is a shorter option copied down or Col E is a bit longer but will spill all the results down from a single formula - no need to copy down.

24 04 04.xlsm
	A	B	C	D	E
1
2	SILVER GROSS WINNER(Stroke)			36 Gross Stroke	36 Gross Stroke
3	SILVER GROSS R/U(Stroke)			36 Gross Stroke	36 Gross Stroke
4	SILVER NETT WINNER (Stroke)			36 Nett Stroke	36 Nett Stroke
5	SILVER NETT R/U (Stroke)			36 Nett Stroke	36 Nett Stroke
6	SILVER SENIOR GROSS WINNER (Stroke)			36 Gross Stroke	36 Gross Stroke
7	SILVER SENIOR NETT WINNER (Stroke)			36 Nett Stroke	36 Nett Stroke
8	SILVER TUESDAY NETT WINNER (Stroke)			TUE Nett Stroke	TUE Nett Stroke
9	SILVER MONDAY NETT WINNER (Stroke)			MON Nett Stroke	MON Nett Stroke
10	BRONZE NETT WINNER (Sford)			36 Nett Sford	36 Nett Sford
11	BRONZE NETT R/U (Sford)			36 Nett Sford	36 Nett Sford
12	BRONZE GROSS WINNER (Sford)			36 Gross Sford	36 Gross Sford
13	BRONZE GROSS R/U (Sford)			36 Gross Sford	36 Gross Sford
14	BRONZE SENIOR GROSS WINNER (Sford)			36 Gross Sford	36 Gross Sford
15	BRONZE SENIOR NETT WINNER (Sford)			36 Nett Sford	36 Nett Sford
16	BRONZE TUESDAY NETT WINNER (Sford)			TUE Nett Sford	TUE Nett Sford
17	BRONZE MONDAY NETT WINNER(Sford)			MON Nett Sford	MON Nett Sford
Extract

Cell Formulas
Range	Formula
E2:E17	E2	=BYROW(A2:A17,LAMBDA(r,LET(x,MID(r,8,3),IF(OR(x={"Mon","Tue"}),x,36)&" "&IF(ISNUMBER(SEARCH("Nett",r)),"Nett","Gross")&" "&INDEX(TEXTSPLIT(r,{"(",")"}),2))))
D2:D17	D2	=LET(x,MID(A2,8,3),IF(OR(x={"Mon","Tue"}),x,36)&" "&IF(ISNUMBER(SEARCH("Nett",A2)),"Nett","Gross")&" "&INDEX(TEXTSPLIT(A2,{"(",")"}),2))
Dynamic array formulas.

 

[Ian McPherson]