Extract Symbol/Text from Variable Strings

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 from The following strings but unsure if possible.
Length and placement of symbol is always a bit different.
I have tried using MID LOOKUP LEN SEARCH but getting very bad results.

The symbol I would need is followed by the last "/" Sign and nothing more after the ":" of each string. (No :XCME)
I placed the desire result followed by the equal sign next to each string also Highlighted it.
Basically it will always be all that is written between the last "/" and last ":" of the string.
The data I need to extract from is over 5000 Rows.


SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50 = /E4DH23
SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL = /EWZ21
SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75 = /EW3K23
BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00 = /E4AH23
SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00 = /E4BH23
SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50 = /E5CH23
BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.00 = /ESM23
BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25 = /EW3K23
SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.75 = /EW3N2
SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00 = /E5DH23

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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Basically it will always be all that is written between the last "/" and last ":" of the string.

According to that rule, the second record with the correct data is the one highlighted in red:
SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50 = /E4DH23
SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL = /EWZ21

Then try:
Dante Amor
AB
1
2SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50 = /E4DH23/E4DH23
3SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL = /EWZ21/EWZ21
4SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75 = /EW3K23/EW3K23
5BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00 = /E4AH23/E4AH23
6SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00 = /E4BH23/E4BH23
7SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50 = /E5CH23/E5CH23
8BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.00 = /ESM23/ESM23
9BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25 = /EW3K23/EW3K23
10SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.75 = /EW3N2/EW3N23
11SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00 = /E5DH23/E5DH23
Hoja8
Cell Formulas
RangeFormula
B2:B11B2="/" & TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2,":",REPT(" ",999),LEN(A2)-LEN(SUBSTITUTE(A2,":",""))),999)),"/",REPT(" ",99)),99))
 
Upvote 0
Solution
According to that rule, the second record with the correct data is the one highlighted in red:


Then try:
Dante Amor
AB
1
2SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50 = /E4DH23/E4DH23
3SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL = /EWZ21/EWZ21
4SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75 = /EW3K23/EW3K23
5BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00 = /E4AH23/E4AH23
6SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00 = /E4BH23/E4BH23
7SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50 = /E5CH23/E5CH23
8BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.00 = /ESM23/ESM23
9BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25 = /EW3K23/EW3K23
10SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.75 = /EW3N2/EW3N23
11SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00 = /E5DH23/E5DH23
Hoja8
Cell Formulas
RangeFormula
B2:B11B2="/" & TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2,":",REPT(" ",999),LEN(A2)-LEN(SUBSTITUTE(A2,":",""))),999)),"/",REPT(" ",99)),99))
Wow Dante, Thats amazing:)
I have been strugling a few days with it.
Thank you so much for the fast answer and solution

Have a great day
Cheers
OP
 
Upvote 1
SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50 = /E4DH23
SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALL = /EWZ21
SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75 = /EW3K23
BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00 = /E4AH23
SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00 = /E4BH23
SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50 = /E5CH23
BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.00 = /ESM23
BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25 = /EW3K23
SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.75 = /EW3N2
SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00 = /E5DH23
Something like:
Book1
AB
12SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50E4DH23
13SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALEWZ21
14SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75EW3K23
15BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00E4AH23
16SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00E4BH23
17SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50E5CH23
18BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.0ESM23
19BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25EW3K23
20SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.7EW3N23
21SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00E5DH23
Sheet1
Cell Formulas
RangeFormula
B12:B21B12=LEFT(RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)),FIND(":",RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)))-1)
 
Upvote 0
Something like:
Book1
AB
12SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50E4DH23
13SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALEWZ21
14SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75EW3K23
15BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00E4AH23
16SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00E4BH23
17SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50E5CH23
18BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.0ESM23
19BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25EW3K23
20SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.7EW3N23
21SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00E5DH23
Sheet1
Cell Formulas
RangeFormula
B12:B21B12=LEFT(RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)),FIND(":",RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)))-1)

I assumed the = /E4DH23 meant it should be the part parsed out, but you said BETWEEN the last / and the next : so I removed the /
 
Upvote 0
Something like:
Book1
AB
12SOLD -1 /ESM23:XCME 1/50 23 MAR 23 (Thursday) (Wk4) /E4DH23:XCME 4010 CALL @6.50E4DH23
13SOLD 4.0 /ESH22:XCME @4720.0 upon Assignment 4.0 /ESH22:XCME 1/50 31 DEC 21 (EOM) /EWZ21:XCME 4720 CALEWZ21
14SOLD -1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 4200 CALL @39.75EW3K23
15BOT +4 /ESM23:XCME 1/50 MAR 23 (Monday) (Wk5) /E4AH23:XCME 4035 CALL @7.00E4AH23
16SOLD -5 /ESM23:XCME 1/50 MAR 23 (Tuesday) (Wk5) /E4BH23:XCME 4015 CALL @15.00E4BH23
17SOLD -2 /ESM23:XCME 1/50 MAR 23 (Wednesday) (Wk5) /E5CH23:XCME 4030 PUT @10.50E5CH23
18BOT +1 /ESM23:XCME 1/50 JUN 23 (AM Settled) /ESM23:XCME 3500 PUT @25.0ESM23
19BOT +1 /ESM23:XCME 1/50 MAY 23 (Wk3) /EW3K23:XCME 3600 PUT @18.25EW3K23
20SOLD -1 /ESU23:XCME 1/50 JUL 23 (Wk3) /EW3N23:XCME 4200 PUT @225.7EW3N23
21SOLD -2 /ESM23:XCME 1/50 MAR 23 (Thursday) (Wk5) /E5DH23:XCME 4050 PUT @10.00E5DH23
Sheet1
Cell Formulas
RangeFormula
B12:B21B12=LEFT(RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)),FIND(":",RIGHT(A12,LEN(A12)-FIND("|",SUBSTITUTE(A12,"/","|",LEN(A12)-LEN(SUBSTITUTE(A12,"/",""))),1)))-1)
Thank you CSmith :) Very Good as well. It is great to know that there are helpful people like you and Dante. Really appreciate you talents:)
Cheers CSmith
OP
 
Upvote 0
Thank you CSmith :) Very Good as well. It is great to know that there are helpful people like you and Dante. Really appreciate you talents:)
Cheers CSmith
OP
Welcome and thank you for the feedback! :)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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