Brian Dunning's FileMaker Custom Functions

SubstituteRange ( text ; beginString ; endString ; replaceString )

Replaces all occurrances of two text strings, along with any text between them, with another value

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

Jim Main   Jim Main
Main Solutions

Share on Facebook Share on Twitter

  Sample input:
SubstituteRange ( text; "<SPAN"; ">"; "" )

where the content of text is:

<SPAN STYLE= "font-size: 14px;" >size14text </SPAN>
<SPAN STYLE= "color: #3333CC;" >bluetext </SPAN>
<SPAN STYLE= "font-weight: bold;" >boldtext </SPAN>
  Sample output:
size14text </SPAN> bluetext </SPAN> boldtext </SPAN>

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

This is equivalent to the 'Substitute' function, with a single wilcard value. It replaces every occurrance in 'text' of 'beginString*endString', with 'replaceString' (where '*' is any text value of any length). Each beginString value is replaced beggining with the first, up to the first endString value encountered. Each subsequent remaining beginString is processed, until no trailing endString is found.
For example, if beginString = "<tr>", endString = "</tr>" and replaceString = "-", the following text would generate the following results:




I've wanted this feature for some time to parse html tags. However, as there is no way to remove text formatting in FM7 (such as size, font, and color) I've also found it useful in conjunction with the 'GetAsCSS' function to remove field formatting. The following formula will remove all formatting from any text field:

Substitute(GetAsCSS (Field), "</SPAN>¶", "")
, "<SPAN"; ">"; "" )

It uses a combination of html and URL-encoding for special characters that you'll need to substitute for (a return (¶) generates <BR>, while an ampersand (&) generates &amp;)



Ron Smith   Ron Smith
Apr 11, 2009
This is a great custom function. However, there is a omission bug in the above code. This line here:

modText &
SubstituteRange(remainText; beginString; endString; replaceString)

should be

modText & replaceString &
SubstituteRange(remainText; beginString; endString; replaceString)

Otherwise, the replaceString is simply removed and nothing inserted.
Andy Haenszel   Andy Haenszel, Columbus
Dec 16, 2010
Is there a way to copy the original string?

I want to remove the string from FieldA, but would like to drop that original value in FieldB when I'm done.

Log in to post comments.


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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 16 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: