Extract variable substring in variable start and end positions within string.

[johobie]

Hi,
Having difficulty extracting substrings from investments statement.
The substring position is so variable, it is beyond my knowledge to obtain reliable results.
I have worked up to 2 pages of formulas and am far down the rabbit hole. I need a fresh perspective. Any help is appreciated.

Variations:
1. Strings started and ended in quotes discovered only when I copied into Notepad. They do not show in the cell.
2. Strings can present on 2 lines, either with most of the string on the first line, and at times with most on the 2nd line.
3. Strings always begin with "CALL-100" or "PUT -100".
4. Substrings sometimes start at left with space, sometimes not.
5. Substrings sometimes have a space or not within the substring.
6. Substrings sometimes have a space in a different position within the substring.
7. Substrings can begin at left with a number and other times with text.
8. Length of substring varies.
9. Substrings sometimes contains @.


STRINGS: EXPECTED RESULT (SUBSTRING):
CALL-100PAAS'21-US 15JA15 OPENING TRANSACTION EXPIRES ON JAN 15,2021 WG-179343 PAAS'21-US 15JA15
CALL-100 BKNG'19 AG@1905 OPENING TRANSACTION EXPIRES ON AUG 09,2019 CE-176463 BKNG'19 AG@1905
CALL-100 SSRM'19-UDC@12.5 CLOSING TRANSACTION EXPIRES ON DEC 20,2019 ZD-183376 SSRM'19-UDC@12.5
"CALL-100NFLX'19 2AG@322.5 CLOSING TRANSACTION EXPIRES ON AUG 02,2019
YL-181770" NFLX'19 2AG@322.5
CALL-100 CSX'19 AG@70 OPENING TRANS - UNCOVERED EXPIRES ON AUG 16,2019 MD-181143 CSX'19 AG@70
CALL-100COST'19 2AG@267.5 CLOSING TRANSACTION EXPIRES ON AUG 02,2019 UM-179132 COST'19 2AG@267.5
"CALL-100NFLX'19 2AG@322.5 OPENING TRANSACTION EXPIRES ON AUG 02,2019
RA-180837" NFLX'19 2AG@322.5
CALL-100 MGA'19-USAG@47.5 OPENING TRANSACTION EXPIRES ON AUG 16,2019 ZG-179495 MGA'19-USAG@47.5
CALL-100 NYT'19 AG@36 NYT'19 AG@36
CALL-100 ABBV'19 AG@72.5 ABBV'19 AG@72.5
CALL-100 ACRS'20 21FB@2.5 ACRS'20 21FB@2.5
CALL-100 AAPL'19 JL@200 AAPL'19 JL@200
CALL-100 GDXJ'20 JA@36 GDXJ'20 JA@36
PUT -100 RCL'19 5JL@119 RCL'19 5JL@119
PUT -100 JD'19 28JN@20 JD'19 28JN@20
PUT -100 NTES'19 28JN@200 NTES'19 28JN@200
PUT -100 ORCL'19 23AG@58 ORCL'19 23AG@58
PUT -100BABA'19 12JL172.5 OPENING TRANSACTION EXPIRES ON JUL 12,2019 RY-178443 BABA'19 12JL172.5
"PUT -100 MGA'19-USAG@42.5 OPENING TRANSACTION EXPIRES ON AUG 16,2019 MGA'19-USAG@42.5
ZF-179657"
PUT -100 IBB'19 AG@106 IBB'19 AG@106
PUT -100 CRON'19-US AG@16 OPENING TRANSACTION EXPIRES ON AUG 16,2019 RD-184511 CRON'19-US AG@16
PUT -100BABA'19 12JL172.5 CLOSING TRANSACTION EXPIRES ON JUL 12,2019 CY-177975 BABA'19 12JL172.5
PUT -100 MGA'19-USAG@47.5 OPENING TRANS - UNCOVERED EXPIRES ON AUG 16,2019 NV-179793 MGA'19-USAG@47.5
"PUT -100AMZN'19 26JL@2010 CLOSING TRANSACTION EXPIRES ON JUL 26,2019 AMZN'19 26JL@2010
GY-179564"
"CALL-100
CGC'19-USAG@37.5 CLOSING TRANSACTION EXPIRES ON AUG 16,2019 AM-182896" CGC'19-USAG@37.5
CALL-100 GOLD'19-US SP@16 CLOSING TRANSACTION EXPIRES ON SEP 20,2019 IQ-183429 GOLD'19-US SP@16
CALL-100PAAS'21-US 15JA15 CLOSING TRANSACTION EXPIRES ON JAN 15,2021 MM-182809 PAAS'21-US 15JA15
"CALL-100
MGA'19-USAG@47.5 CLOSING TRANSACTION EXPIRES ON AUG 16,2019 CF-186950" MGA'19-USAG@47.5
PUT -100AMZN'19 30AG@1740 OPENING TRANSACTION EXPIRES ON AUG 30,2019 LC-186015 100AMZN'19 30AG@1740
CALL-100 CBLK'20 $ AP@25 AS OF OCT 18,2019 EXPIRES ON OCT 18,2019 EXERC 6 CBLK200417C25 CBLK'20 $ AP@25
PUT -100NOW'19 29NV@272.5 OPENING TRANSACTION EXPIRES ON NOV 29,2019 NE-200252 NOW'19 29NV@272.5
PUT -100AVGO'20 3JA@312.5 AVGO'20 3JA@312.5
CALL-100 20MASI'19 DC@150 OPENING TRANSACTION EXPIRES ON DEC 20,2019 FG-177698 20MASI'19 DC@150

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B
1	CALL-100PAAS'21-US 15JA15 OPENING TRANSACTION EXPIRES ON JAN 15,2021 WG-179343	PAAS'21-US 15JA15
2	CALL-100 BKNG'19 AG@1905 OPENING TRANSACTION EXPIRES ON AUG 09,2019 CE-176463	BKNG'19 AG@1905
3	CALL-100 SSRM'19-UDC@12.5 CLOSING TRANSACTION EXPIRES ON DEC 20,2019 ZD-183376	SSRM'19-UDC@12.5
4	"CALL-100NFLX'19 2AG@322.5 CLOSING TRANSACTION EXPIRES ON AUG 02,2019 YL-181770"	NFLX'19 2AG@322.5
5	CALL-100 CSX'19 AG@70 OPENING TRANS - UNCOVERED EXPIRES ON AUG 16,2019 MD-181143	CSX'19 AG@70
6	CALL-100COST'19 2AG@267.5 CLOSING TRANSACTION EXPIRES ON AUG 02,2019 UM-179132	COST'19 2AG@267.5
7	"CALL-100NFLX'19 2AG@322.5 OPENING TRANSACTION EXPIRES ON AUG 02,2019 RA-180837"	NFLX'19 2AG@322.5
8	CALL-100 MGA'19-USAG@47.5 OPENING TRANSACTION EXPIRES ON AUG 16,2019 ZG-179495	MGA'19-USAG@47.5
9	CALL-100 NYT'19 AG@36	NYT'19 AG@36
10	CALL-100 ABBV'19 AG@72.5	ABBV'19 AG@72.5
11	CALL-100 ACRS'20 21FB@2.5	ACRS'20 21FB@2.5
12	CALL-100 AAPL'19 JL@200	AAPL'19 JL@200
13	CALL-100 GDXJ'20 JA@36	GDXJ'20 JA@36
14	PUT -100 RCL'19 5JL@119	RCL'19 5JL@119
15	PUT -100 JD'19 28JN@20	JD'19 28JN@20
16	PUT -100 NTES'19 28JN@200	NTES'19 28JN@200
17	PUT -100 ORCL'19 23AG@58	ORCL'19 23AG@58
18	PUT -100BABA'19 12JL172.5 OPENING TRANSACTION EXPIRES ON JUL 12,2019 RY-178443	BABA'19 12JL172.5
19	"PUT -100 MGA'19-USAG@42.5 OPENING TRANSACTION EXPIRES ON AUG 16,2019 MGA'19-USAG@42.5ZF-179657"	MGA'19-USAG@42.5
20	PUT -100 IBB'19 AG@106 IBB'19 AG@106	IBB'19 AG@106 IBB'19 AG@106
21	PUT -100 CRON'19-US AG@16 OPENING TRANSACTION EXPIRES ON AUG 16,2019 RD-184511 CRON'19-US AG@16	CRON'19-US AG@16
22	PUT -100BABA'19 12JL172.5 CLOSING TRANSACTION EXPIRES ON JUL 12,2019 CY-177975 BABA'19 12JL172.5	BABA'19 12JL172.5
23	PUT -100 MGA'19-USAG@47.5 OPENING TRANS - UNCOVERED EXPIRES ON AUG 16,2019 NV-179793 MGA'19-USAG@47.5	MGA'19-USAG@47.5
24	"PUT -100AMZN'19 26JL@2010 CLOSING TRANSACTION EXPIRES ON JUL 26,2019 AMZN'19 26JL@2010GY-179564"	AMZN'19 26JL@2010
25	"CALL-100 CGC'19-USAG@37.5 CLOSING TRANSACTION EXPIRES ON AUG 16,2019 AM-182896" CGC'19-USAG@37.5	CGC'19-USAG@37.5
26	CALL-100 GOLD'19-US SP@16 CLOSING TRANSACTION EXPIRES ON SEP 20,2019 IQ-183429 GOLD'19-US SP@16	GOLD'19-US SP@16
27	CALL-100PAAS'21-US 15JA15 CLOSING TRANSACTION EXPIRES ON JAN 15,2021 MM-182809 PAAS'21-US 15JA15	PAAS'21-US 15JA15
28	"CALL-100 MGA'19-USAG@47.5 CLOSING TRANSACTION EXPIRES ON AUG 16,2019 CF-186950"	MGA'19-USAG@47.5
29	PUT -100AMZN'19 30AG@1740 OPENING TRANSACTION EXPIRES ON AUG 30,2019 LC-186015 100AMZN'19 30AG@1740	AMZN'19 30AG@1740
30	CALL-100 CBLK'20 $ AP@25 AS OF OCT 18,2019 EXPIRES ON OCT 18,2019 EXERC 6 CBLK200417C25 CBLK'20 $ AP@25	CBLK'20 $ AP@25
31	PUT -100NOW'19 29NV@272.5 OPENING TRANSACTION EXPIRES ON NOV 29,2019 NE-200252 NOW'19 29NV@272.5	NOW'19 29NV@272.5
32	PUT -100AVGO'20 3JA@312.5	AVGO'20 3JA@312.5
33	CALL-100 20MASI'19 DC@150 OPENING TRANSACTION EXPIRES ON DEC 20,2019 FG-177698 20MASI'19 DC@150	20MASI'19 DC@150
Sheet29

Cell Formulas
Range	Formula
B1:B33	B1	=TRIM(MID(A1,SEARCH("100",A1)+3,MIN(IFERROR(SEARCH("AS OF",A1),1000),IFERROR(SEARCH("OPENING",A1),1000),IFERROR(SEARCH("CLOSING",A1),1000))-1-SEARCH("100",A1)-3))

Messy.