These last weeks I have looked at parsing text. After countless hours and having studied many splendid tips, the like of Text Parsing Tutorial by Ugo Di Luca (recommended), I must concede defeat on this one! Can any one offer help or a solution that can be applied using FMP 5.5 to the following problem.

Problem, I need to come up with a Calculation that will parse data between colon 2 and 3 into a global text field as a list i.e. each string separated by a carriage return. Using a looping script is not an option.

gData

Prefix 1:one or more words:one or more words:

Prefix 1:one or more words:one or more words:

Prefix 1:one or more words:one or more words:

Prefix 2:two or more words:one or more words:

Prefix 2:two or more words:one or more words:

gSeperator : (colon, no leading or trailing space)

Desired result needs to be dynamic so that when field _prefix contains Prefix 1

glist = one or more words

one or more words

one or more words

And when _prefix contains Prefix 2

gList = two or more words

two or more words

My best attempt so far returns the first row of data only, attempts to extend the calculation below using PatternCount have not been successful.

My calculation

Middle(

gSeperator& gData & gSeperator,

Position (gSeperator& gData & gSeperator, gSeperator, 2, 2 ) + 1,

Position (gSeperator& gData & gSeperator, gSeperator, 2, 2 ) + 1) -

Position (gSeperator& gData & gSeperator, gSeperator, 2, 2 ) - 1)

Result

gList = one or more words

Thank you in advance - Steve

Steve,

1) Do you know in advance how many rows or occurrence of each Prefix would show in that list ?

2) Are the prefix ordered so that Prefix_n+1 is still following Prefix_n ?

Thanks you for the good words.

Hi,

Just in case this may help.

You can split it at first in several calcs and then join them.

Extraction 1 = Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;1);Position(data & field_prefix;field_prefix;1;2)-Position(data & field_prefix;field_prefix;1;1)-1)

Extraction 2 = Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;2);Position(data & field_prefix;field_prefix;1;3)-Position(data & field_prefix;field_prefix;1;2)-1)

Extraction n =

Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n);Position(data & field_prefix;field_prefix;1;n+1)-Position(data & field_prefix;field_prefix;1;n)-1)

will extract all lines from the data field.

Then

Parsing 1 =

Middle(Extraction 1 & gSep;POSITION(Extraction 1 & gSep;gSep;1;2)+1;POSITION(Extraction 1 & gSep;gSep;1;3)-POSITION(Extraction 1 & gSep;gSep;1;2)-1)

Parsing 2 =

Middle(Extraction 2 & gSep;POSITION(Extraction 2 & gSep;gSep;1;2)+1;POSITION(Extraction 2 & gSep;gSep;1;3)-POSITION(Extraction 2 & gSep;gSep;1;2)-1)

Parsing n =

Middle(Extraction n & gSep;POSITION(Extraction n & gSep;gSep;1;2)+1;POSITION(Extraction n & gSep;gSep;1;3)-POSITION(Extraction n & gSep;gSep;1;2)-1)

When joined you'd get :

Parsing n =

Middle(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n);Position(data & field_prefix;field_prefix;1;n+1)-Position(data & field_prefix;field_prefix;1;n)-1) & gSep;POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n);Position(data & field_prefix;field_prefix;1;n+1)-Position(data & field_prefix;field_prefix;1;n)-1) & gSep;gSep;1;2)+1;POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n);Position(data & field_prefix;field_prefix;1;n+1)-Position(data & field_prefix;field_prefix;1;n)-1) & gSep;gSep;1;3)-POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n);Position(data & field_prefix;field_prefix;1;n+1)-Position(data & field_prefix;field_prefix;1;n)-1) & gSep;gSep;1;2)-1)

and

Parsing n+X =

Middle(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n+X);Position(data & field_prefix;field_prefix;1;n+1+X)-Position(data & field_prefix;field_prefix;1;n+X)-1) & gSep;POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n+X);Position(data & field_prefix;field_prefix;1;n+1+X)-Position(data & field_prefix;field_prefix;1;n+X)-1) & gSep;gSep;1;2)+1;POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n+X);Position(data & field_prefix;field_prefix;1;n+1+X)-Position(data & field_prefix;field_prefix;1;n+X)-1) & gSep;gSep;1;3)-POSITION(Middle(data & field_prefix;Position(data & field_prefix;field_prefix;1;n+X);Position(data & field_prefix;field_prefix;1;n+1+X)-Position(data & field_prefix;field_prefix;1;n+X)-1) & gSep;gSep;1;2)-1)

Which mean a 4 lines extraction, substituting X with a number would lead to :

Parsing n+0

&"