GetTextBetween ( text ; from ; to ; occurrence )

Gets the text between the nth "occurence" of string "from" and string "to"

Average rating: 4.0 (39 votes) Log in to vote

Ulf Carlsson - Show more from this author
Studentlitteratur AB
http://www.studentlitteratur.se

Sample input:

GetTextBetween ( "abcdxfghdefg hfd", "d", "f", 2 )

Sample output:

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

/* Copyright Ulf Carlsson, ulf.carlsson@studentlitteratur.se ( Free to use if you don't delete this line )

Function: GetTextBetween
Parameters:
  text              --  the text to parse
  from             --  get all text after this text
  to                 --  get all text to this string
  occurrence    -- get the nth occuernce in the text

*/

Let ( [
    occurrence = If (
                          IsEmpty ( occurrence ) or  occurrence  ≤ 0 ; 1 ;    // If occerence isn't 1 or more, set it to one
                          Int ( occurrence ) ) ;

   startPos = If (
                      IsEmpty ( from ) ; 1 ;      // If parameter "from" is empty start from the very begining
                      Position ( text ; from ; 1 ; occurrence ) + Length ( from ) ) ;

    endPos =  If (
                      IsEmpty ( to ) ; Length ( text ) ;  // If parameter "to" is empty include to the very end
                      Position ( text ; to ; startPos ; 1 ) )

 ] ; 
   Case ( 
        startPos = Length ( from ) ; "" ;  //  "from" is not found in the text, this should return an empty string
        endPos = startPos ; "" ;              //  "to" is not found in the text,  this should return an empty string
        Middle ( text ; startPos ; endPos - startPos ) // Otherwise return string
    )
)

Problem: You want to parse a html-file to extract some information
Solution: Use GetTextBetween!
A more advanced example:

Extract from a html-page:
.....
Actor:
Russell Crowe
Joaquin Phoenix
Djimon Hounsou
Connie Nielsen
Richard Harris
Derek Jacobi
Oliver Reed
....

GetTextBetween ( pageAbove ; "movies,,,\">", "", 5) = "Richard Harris"
GetTextBetween ( pageAbove ; "movies,,,\">", "", 3) = "Djimon Hounsou"

Comments

		Bill S, NY Jun 9, 2014
I tried to parse the following HTML in order to get what is between the frist </ul> and the third <h2>. Unfortunatly i get the right result short of the closing ul tag. Do you know what i might be doing wrong? <h2>Milwaukee 48-59-1812 Features</h2> <ul><li>Charger compatible with M12 battery packs: 48-11-2401, 48-11-2402</li></ul> <h2>Milwaukee 48-59-1812 Specifications</h2> <ul><li>Model: 48-59-1812</li></ul> <h2>Milwaukee 48-59-1812 Includes</h2> <ul><li>(1) M18 and M12 Li-Ion Battery Charger</li></ul> Any help would be greatly appreciated?

		unix, Japan Nov 11, 2014
There is only one occurrence parameter, it is used for starting position. So you can't use as 'the third <h2>'. Add one more parameter to specify ending position, or change the place between 2 Position function.

		Rich, Wales Jun 26, 2015
Thanks for this, saved my a lot of effort

		Ulf Carlsson, Lund Jun 29, 2015
Hello! Remember that you can use more characters to get unique start and stop places. I would try having "</li></ul> <h2>" as stop point. best regards, Ulf

		Ulf Carlsson, Lund Jun 29, 2015
Hello again! I have also in some cases first found the text i wanted by first finding a bigger surrounding text, when i got that, it was easier to find the text I wanted inside the fist text. Perhaps you can use that method? Regards Ulf

		Bert Duivestein, Zwolle Aug 28, 2017
Thanks! Works great to derive tags from a XML file.

		Binu Alexander, SevenX Ltd. Aug 20, 2020
This is a great ! saved hours of my time.

		Kevin B Aug 18, 2022
How would I extract values where quotation marks are used in the search text?

		Kevin B Aug 18, 2022
How would I extract values where quotation marks are used in the search text?

		Kevin B Aug 18, 2022
Sorry...I figured it out with a "Substitute" function...

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

GetTextBetween ( text ; from ; to ; occurrence )

Comments

Support this website.

Support this website.