Retrieve Left Symbol from String

O

ONP Nino

New Member
Joined
Apr 2, 2018
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
How about
Fluff.xlsm
AB
1
2SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50ESM23:XCME
3SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALLESH22:XCME
4SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75ESM23:XCME
5BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00ESM23:XCME
6SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00ESM23:XCME
7/ESM20:XCME mark to market at 2941.00 official settlement priceESM20:XCME
8tAndroid BOT +1 /ESM20:XCME @2922.00ESM20:XCME
9SOLD -10 FUT CALENDAR /MESH21:XCME - /MESZ20:XCME @-8.00MESH21:XCME
10/MESM21:XCME mark to market at 4193.80 official settlement priceMESM21:XCME
11SOLD -5 /MESU21:XCME @4200.00MESU21:XCME
Dashboard
Cell Formulas
RangeFormula
B2:B11B2=LEFT(REPLACE(A2,1,FIND("/",A2),""),FIND(" ",REPLACE(A2,1,FIND("/",A2),""))-1)
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
AB
1
2SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50ESM23:XCME
3SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALLESH22:XCME
4SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75ESM23:XCME
5BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00ESM23:XCME
6SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00ESM23:XCME
7/ESM20:XCME mark to market at 2941.00 official settlement priceESM20:XCME
8tAndroid BOT +1 /ESM20:XCME @2922.00ESM20:XCME
9SOLD -10 FUT CALENDAR /MESH21:XCME - /MESZ20:XCME @-8.00MESH21:XCME
10/MESM21:XCME mark to market at 4193.80 official settlement priceMESM21:XCME
11SOLD -5 /MESU21:XCME @4200.00MESU21:XCME
Dashboard
Cell Formulas
RangeFormula
B2:B11B2=LEFT(REPLACE(A2,1,FIND("/",A2),""),FIND(" ",REPLACE(A2,1,FIND("/",A2),""))-1)
Click to expand...
Brilliant Fluff:)
Works super.
Thank you so much for the quick solution.

Have a great Day:)
Cheers
OP
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
How about
Excel Formula: 
=LEFT(REPLACE(A2,1,FIND("/",A2)-1,""),FIND(" ",REPLACE(A2,1,FIND("/",A2),""))-1)
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=LEFT(REPLACE(A2,1,FIND("/",A2)-1,""),FIND(" ",REPLACE(A2,1,FIND("/",A2),""))-1)
Click to expand...

ONP Nino said:
Thank you very much Fluff
Click to expand...
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

In any case, what about this one?
Excel Formula: 
=REPLACE(LEFT(A2,FIND(" ",A2,FIND("/",A2))-1),1,FIND("/",A2)-1,"")
 
Upvote 0
Peter_SSs said:
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

In any case, what about this one?
Excel Formula: 
=REPLACE(LEFT(A2,FIND(" ",A2,FIND("/",A2))-1),1,FIND("/",A2)-1,"")
Click to expand...
Hi Peter,
I am glad you posted your formula. It works super. I just had a chance to test both formulas But 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. I really don't get it.
=LEFT(REPLACE(D35,1,FIND("/",D35)-1,""),FIND(" ",REPLACE(D35,1,FIND("/",D35),""))-1)
Yours is working well and I thank you:)
I get the result that I need:)
Thank you so much Peter. And thank you both for helping:)
Cheers
OP
/ESZ21:XCME
 
Upvote 0
Hi @ONP Nino.
A pleasure to greet you again.​

I show you the result of the formulas. My contributions in columns B and C.

Dante Amor
ABCDE
1damdampeterfluff
2SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50/ESM23:XCME/ESM23:XCME/ESM23:XCME/ESM23:XCM
3SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL/ESH22:XCME/ESH22:XCME/ESH22:XCME/ESH22:XCM
4SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75/ESM23:XCME/ESM23:XCME/ESM23:XCME/ESM23:XCM
5BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00/ESM23:XCME/ESM23:XCME/ESM23:XCME/ESM23:XCM
6SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00/ESM23:XCME/ESM23:XCME/ESM23:XCME/ESM23:XCM
7/ESM20:XCME mark to market at 2941.00 official settlement price/ESM20:XCME/ESM20:XCME/ESM20:XCME/ESM20:XCM
8tAndroid BOT +1 /ESM20:XCME @2922.00/ESM20:XCME/ESM20:XCME/ESM20:XCME/ESM20:XCM
9SOLD -10 FUT CALENDAR /MESH21:XCME - /MESZ20:XCME @-8.00/MESH21:XCME/MESH21:XCME/MESH21:XCME/MESH21:XCM
10/MESM21:XCME mark to market at 4193.80 official settlement price/MESM21:XCME/MESM21:XCME/MESM21:XCME/MESM21:XCM
11SOLD -5 /MESU21:XCME @4200.00/MESU21:XCME/MESU21:XCME/MESU21:XCME/MESU21:XCM
Hoja4
Cell Formulas
RangeFormula
B2:B11B2=MID(A2,FIND("/",A2),FIND(" ",A2,FIND("/",A2))-FIND("/",A2))
C2:C11C2=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("/",A2),99)," ",REPT(" ",99)),99))
D2:D11D2=REPLACE(LEFT(A2,FIND(" ",A2,FIND("/",A2))-1),1,FIND("/",A2)-1,"")
E2:E11E2=LEFT(REPLACE(A2,1,FIND("/",A2)-1,""),FIND(" ",REPLACE(A2,1,FIND("/",A2),""))-1)


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Extract Symbol/Text from Variable Strings
Replies
6
Views
516
CSmith
CSmith

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top