Brian Dunning's FileMaker Custom Functions

text.betweenNext ( _text ; _searchString1 ; _occurrence1 ; _include1.b ; _searchString2 ; _occurrence2 ; _include2.b )

extracts the middle of a text in between delimiters, based on search strings and an occurrence numbers

  Average rating: 4.3 (38 votes) Log in to vote

Fabrice Nordmann   Fabrice Nordmann
1-more-thing
https://www.1-more-thing.com

Share on Facebook Share on Twitter

  Sample input:
BetweenNext ( "A 1234567890 X B 1234567890 X C 1234567890 X" ; "B" ; 1 ; 1 ; "X" ; 1 ; 0 )
  Sample output:
"B 1234567890 "

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

extracts the middle of a text in between delimiters, based on a search strings and an occurrence numbers
occurrences can be positive (starting from the beginning of the text) or negative (starting from the end)
The second string will be found only after the first one.
Both string can be included or excluded with boolean parameters.

e.g : text.betweenNext ( "A 1234567890 X B 1234567890 X C 1234567890 X" ; "B" ; 1 ; 1 ; "X" ; 1 ; 0 ) = "B 1234567890 "

see also text.between, text.before and text.after functions

 

Comments

Carl Riedel   Carl Riedel, Croydon, UK
Jul 10, 2011
This code works beautifully.
 
Harald Koeppen   Harald Koeppen, Frankfurt Germany
Nov 6, 2011
superbly written. incredible useful.
 
Tobias Sjögren   Tobias Sjögren, Stockholm
Jan 24, 2012
Wouldn't it be great to have a "BetweenPrevious" function as well?
 
Fabrice   Fabrice, Brussels
Jan 24, 2012
BetweenPrevious is included in the "Pro" pack available on our web store (no, just kidding ;))
Please feel free to modify this function and share it!
Fabrice Nordmann
 
HazMatt   HazMatt, Blaine, MN, USA
Jan 22, 2013
This function works (nearly) perfectly for parsing out values from a web result like this:
field1=value1&field2=value2&field3=value3

The only problem is that when I plug in "field3=" for _searchString1 and "&" for _searchString2, I get a blank result (because there is no "&" character to look for on the tail end).

I modified the custom function to include this bit in the Let() statement:

Let ([
_text = _text & If(Right(_text; 1) ≠ _searchString2; _searchString2; "")
; _occurrence1 = Case ( _occurrence1 = 0 ; 1 ; _occurrence1 )

Would this be a good addition to your custom function, Fabrice? Or would it mess up in some cases?
 
Katy Butterworth   Katy Butterworth, New Zealand
Sep 22, 2013
This custom function is a masterpiece!
 
Fabrice Nordmann   Fabrice Nordmann, 1-more-thing
Sep 22, 2013
Thank you Katty.
Matt, I didn't see your post, sorry.
I like a function not to do hidden stuff, otherwise it becomes more difficult to use in certain situations, although it's easier in some others.
In your situation I would rather send a different parameter such as [your string] & "&".
By the way something is broken in 12 with the Position function. You should change the constant 1 with 0 so this function and others keep working in all scenarios
An updated version can be found on fmfunctions.com
 
Lewis Lorenz   Lewis Lorenz, Lorenz Companies
Sep 15, 2017
Next to the bulit-in functions in FileMaker, this function is the one I use most in my document assembly scripts. It is indispensable! I added two lines (suffixed with "added") to your code to return the searchstrings as they appear in the text. It aids in using the Substitute function. Thank you.

; _p1= Position ( _text ; _searchString1 ; Case ( _occurrence1 < 0 ; Length ( _text ) ; 1 ) ; _occurrence1 )
; _searchString1 = If ( _p1 ; Middle ( _text ; _p1 ; _lenStr1 ) ; _searchString1 ) //Added.
; _rightText = Replace ( _text ; 1 ; _p1 + _lenStr1 - 1 ; "" )
; _p2= Position ( _rightText ; _searchString2 ; Case ( _occurrence2 < 0 ; Length ( _rightText ) ; 1 ) ; _occurrence2 )
; _searchString2 = If ( _p2 ; Middle ( _rightText ; _p2 ; Length ( _searchString2 ) ) ; _searchString2 ) //Added.
 

Log in to post comments.

 

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

Under construction. Email me your wish list for improvements.