Retrieve Left Symbol from String

[ONP Nino]

Hello,

I would like to retrieve/extract The Symbol Highlighted in Red from The following strings. Basically it will always be from the first "/" starting from Left
Length and placement of symbol is always a bit different.
Just Recently A Well KnownMember here helped me retrieving the far right Symbol starting at "/" and ending at ":". with the following formula
="/" & TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2,":",REPT(" ",999),LEN(A2)-LEN(SUBSTITUTE(A2,":",""))),999)),"/",REPT(" ",99)),99))
Formula works great, but now I am in need of the Left symbol.

SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50
SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL
SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75
BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00
SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00
/ESM20:XCME mark to market at 2941.00 official settlement price
tAndroid BOT +1 /ESM20:XCME @2922.00
SOLD -10 FUT CALENDAR /MESH21:XCME - /MESZ20:XCME @-8.00
/MESM21:XCME mark to market at 4193.80 official settlement price
SOLD -5 /MESU21:XCME @4200.00


Using MS Office Excel 2019
Thank you for your help. It is greatly appreciated
Cheers
OP

 

[Peter_SSs]

I am glad you posted your formula. It works super.

You're welcome. Glad to contribute. As you can see there are many ways to arrive at the desired result.

for some reason I am unable to See a result in the cell from Fluff's code. Hmm, not sure whats happening??? I get the full formula as a result.

Perhaps that cell or column is formatted as Text and not as General?

 

[Tom.Jones]

Hi,

In 2019 have you TEXTBEFORE and TEXTAFTER? Formula will be easy.

 

[Peter_SSs]

In 2019 have you TEXTBEFORE and TEXTAFTER?

Only 365 I believe.

 

[Fluff]

Doesn't that last formula remove the last character of the required extraction? That is, it returns /ESM23:XCM for cell A2 and not /ESM23:XCME

Good spot Peter, forgot to change the 2nd Replace, so it should be

=LEFT(REPLACE(A2,1,FIND("/",A2)-1,""),FIND(" ",REPLACE(A2,1,FIND("/",A2)-1,""))-1)