PositionValues ( text ; searchValue ; occurrence )

Return value position (line number) in list of all occurrences of searchValue

Average rating: 4.1 (40 votes) Log in to vote

Bruce Robertson - Show more from this author

Sample input:

positionValues( "A¶B¶A¶C¶D"; "A¶D"; 1)

Sample output:

1
3
5

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
PositionValues( text; searchValue; occurrence)

Returns value position in text of all occurrences of searchvalue
Search value can be a list of values

positionValues( "A¶B¶A¶C¶D"; "A¶D"; 1) 
=> 
1
3
5
Bruce Robertson, Concise Design
v 1.0 5/25/2010
*/

Let ([
adjustedText = "¶" & Substitute( text; "¶"; "¶¶" ) & "¶" ;
thisValue = GetValue( searchValue; 1) ;
target = "¶" & thisValue & "¶"  ;
P1 = Position ( adjustedText ; target ; 1 ; occurrence );
P2 = Position ( adjustedText ; target ; 1 ; occurrence +1 );
SVCount = ValueCount( searchValue);
chunk = Middle ( adjustedtext ; 2; P1  + Length ( thisValue )  ) ;
result = Case( P1 ; ValueCount ( Substitute( chunk; "¶¶"; "¶" )))  ];

List( 
Result ; 
Case( P2 ; PositionValues( text; thisvalue; occurrence+1));
Case( SVCount>1 ; PositionValues( text; MiddleValues( searchValue; 2; ValueCount( searchValue)); 1))
)
)

Multi-value version of the positionValue function. Return the line number of all items in a list where the item = searchValue. Usually you will want to enter 1 for occurrence to find all values. If you enter a number other than one, and your searchValue is in the form of a list, the offset will only be honored for the first item in the list.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

PositionValues ( text ; searchValue ; occurrence )

Comments

Support this website.

Support this website.