Complex substring selections

[sifatnabil]

Hi, I am trying to select particular substrings from a long string, but can't seem to formulate a way of doing this. This is the string, and colour-coded substrings I'd like to select:

"Received Order Insert message from gbarnfieldfsn. Instrument: 1M KRW Fix Midpoint, Mkt: 7. Order: Bid 10 @ 1.37."

I want to make new fields and input these substrings in them like below:

Instrument, B/O, Amount, Price

1M KRW, Bid, 10, 1.37

I can think of different operators like trim, index, right, left, len, etc. but finding it very difficult to select these without wildmatches. Any help greatly appreciated!

[Gysbert_Wassenaar]

Something like this:

load

     Instrument,

     subfield(Rest,' ',1) as [B/O],

     subfield(Rest,' ',2) as Amount,

     subfield(Rest,' ',3) as Price;

load

     textbetween(MyString, 'Instrument: ','Fix') as Instrument,

     subfield(MyString,':',-1) as Rest

from ....

[sifatnabil]

Hi Gysbert,

Thanks a lot for this. However I'm getting it slightly misplaced like below:

I'm getting Amount under Price and B/O under Amount:

Instrument	B/O	Amount	Price
1M MYR		Offer	50
1M CNY		Offer	50
1M MYR		Offer	3

[Gysbert_Wassenaar · Accepted solution]

load

     MyString,Rest,

     Instrument,

     subfield(Rest,' ',2) as [B/O],

     subfield(Rest,' ',3) as Amount,

     subfield(Rest,' ',5) as Price;

load

     MyString,

     textbetween(MyString, 'Instrument: ','Fix') as Instrument,

     subfield(MyString,':',-1) as Rest;

load

     left(MyString, len(MyString)-1 ) as MyString

from ....

[sifatnabil]

Perfect, thanks!