Parsing multiple occurrences of a variable string

Discussion created by user25648 on Jul 21, 2016
Latest reply on Jul 21, 2016 by user25648

I have a large text record for which I have manipulated the text based upon patterns. 


"...blahblahblah blah blah item no. xxxxxx, blah blah blah..."


is manipulated to become


"...blahblahblah blah blah

ITEM xxxxxx

blah blah blah..."


From here I extract the actual number (xxxxxx) by using ParseBetween ( text ; "ITEM" ; ¶ )


These records are variable in length, values and content.  Though they consistently have at least one ITEM number, they can have many ITEM numbers appearing in varying locations within the large text record.


My problem is, some of my records have more than one ITEM number.  I'm already extracting the first occurrence of the ITEM number using the ParseBetween calculation mentioned above.  But, I need to take things further to do two more things (for two different and unique purposes):

1.  how do I extract the second occurrence of ITEM xxxxx into a second field?

2.  in cases where I have more than two occurrences of ITEM xxxxx, how do I create a list of the values?


Bear in mind, that the ITEM number is only one of many elements I am parsing from each record.