ph_replaceregex3 PowerHome formula function
Description
Uses C# regular expressions to selectively match and replace elements within the supplied input string 
Syntax
ph_replaceregex3 ( as_pattern, as_replace, as_data, al_start, al_occur, ai_flags, ai_localstart, ai_locallength, ai_localreplength )
Argument Description
as_pattern String. The regular expression pattern to match
as_replace String. The replace (substitution) pattern to replace matches with
as_data String. The input string of data you wish to perform regular expression search/replace operations on
al_start Long. The starting position in which to apply the regular expression search pattern. Use 1 to start at the first character
al_occur Long. The number of search and replacements you would like to perform. To perform all possible replacements, use 0. To perform only a single search and replacement, use 1, etc. Note that this parameter is different from the other RegEx3 functions in that it specifies how many times to do a search and replace operation rather than specifying to do a search and replace only on a particular occurence.
ai_flags Integer. A flags parameter that controls how the regular expression is performed
ai_localstart Integer . The index of a local variable in which to store the start of data that is matched and replaced by the regular expression. Use 1 thru 10 for [LOCAL1] thru [LOCAL10]. If you don't want the start position stored in a local variable, use 0. This parameter is ONLY relevant if the al_occur parameter is 1. Any other value for al_occur will return a 0 in the specified LOCAL variable
ai_locallength Integer . The index of a local variable in which to store the length of data that is matched by the regular expression before replacement. Use 1 thru 10 for [LOCAL1] thru [LOCAL10]. If you don't want the length of matched data stored in a local variable, use 0. This parameter is ONLY relevant if the al_occur parameter is 1. Any other value for al_occur will return a 0 in the specified LOCAL variable
ai_localreplength Integer . The index of a local variable in which to store the length of the replacement data that was performed by the regular expression. Use 1 thru 10 for [LOCAL1] thru [LOCAL10]. If you don't want the replacement length of matched data stored in a local variable, use 0. This parameter is ONLY relevant if the al_occur parameter is 1. Any other value for al_occur will return a 0 in the specified LOCAL variable
Return value
String. Returns the as_data input string after the appropriate search and replacements have been performed 
Usage
Use this function to search and replace data within a supplied string using C# regular expression operations as the basis of the search and replacement. See this link for an explanation on regular expression substitutions: https://docs.microsoft.com/en-us/dotnet/standard/base-types/substitutions-in-regular-expressions

This function is useful for search and replacement using regular expressions when you want a finer level of control instead of just performing a brute force search and replace ALL using the ph_replaceallregex3( ) function. You can still achieve the same effect as ph_replaceallregex3 by setting the al_occur parameter to 0 albeit you'll have to supply all the other parameters required by this function

The ai_flags parameter controls how the regular expression search is done. The value for each desired option below would be added together to arrive at the proper ai_flags value. If none of the additional options are required, then use an ai_flags value of 0. For a detailed explanation of eachof the options, see this link: https://docs.microsoft.com/en-us/dotnet/api/system.text.regularexpressions.regexoptions?view=netcore-3.1

  • 1 - Ignore Case
  • 2 - Multiline
  • 4 - Explicit Capture
  • 8 - Compiled
  • 16 - Single Line
  • 32 - Ignore Pattern Whitespace
  • 64 - Right to Left
  • 256 - ECMA Script
  • 512 - Culture Invariant
    • Examples
    The following examples demonstrate typical syntax/usage for this function.
    • ph_replaceregex3(" [a-f]+[^a-f]*","*XX*","abc This is a bc test def",1,0,0,1,2,3) - Returns "abc This is*XX*bc test*XX*". This example searches for any occurence of one more letters of "a" thru "f" following a space and itself being followed by 0 or more characters that are not "a" thru "f". All searches that match this criteria are replaced with the string "*XX*". The first "abc" is not replaced because it was not preceeded by a space character. The " a " in "This is a " matches the search pattern (space followed by 1 or more a-f characters followed by 0 or more characters other than a-f) and so is replaced. The "bc" is NOT matched because the space character that preceeded it was no longer there as it had been replaced by the "*XX*" in the prior match so it is skipped. The "e" in "test" is also not matched due to the lack of a space character. The final " def" is matched and is thus replaced. The [LOCAL1], [LOCAL2], and [LOCAL3] variables will all contain 0 because the al_occur parameter is NOT equal to 1