Extract Symbol/Text from Variable Strings

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 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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
ONP Nino said:
Basically it will always be all that is written between the last "/" and last ":" of the string.
Click to expand...

According to that rule, the second record with the correct data is the one highlighted in red:
ONP Nino said:
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
Click to expand...

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
DanteAmor said:
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))
Click to expand...
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
ONP Nino said:
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
Click to expand...
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
CSmith said:
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)
Click to expand...

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
CSmith said:
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)
Click to expand...
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
ONP Nino said:
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
Click to expand...
Welcome and thank you for the feedback! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Retrieve Left Symbol from String
2
Replies
13
Views
758
Fluff
Fluff
H
  • Solved
Extract text from a string of text based on the range defined
Replies
9
Views
1K
Peter_SSs
Peter_SSs
JeffGrant
  • Solved
How can i extract a variable number of characters from the rhs and keep the result as a string?
Replies
3
Views
709
JeffGrant
JeffGrant
B
  • Question Question
Extract text from variable string length
Replies
9
Views
1K
wideboydixon
wideboydixon
H
  • Question Question
VBA Help - Extracting text from massive string
Replies
4
Views
872
yky
Y

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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