Replace/Remove the first substring of a string that matches the search term or expression using Salesforce Formula.

In Apex String class, we have replaceFirst method to replace the first substring of a string that matches the regular expression with the replacement sequence replacement. When it comes to Salesforce formula, though, we don’t have any function to do the same thing.

I have a requirement to write a flow formula to remove the first occurrence of a substring from a string, say remove the first “ll” from a String “hello y’all”. so it becomes “heo y’all“ here are the steps:

String: hello y’all

Substring to remove: ll

Step 1: FIND('ll','hello y'all')
        //Find the position of the first occurance of 'll' within 'hello y'all'
        

Step 2: LEFT('hello y'all', FIND('ll','hello y'all')-1)
        //Get the sub string until the first occurrence of “ll“ which is "he" 
        //FIND('ll','hello y'all')-1 to exclude 'll'

Step 3: RIGHT(hello y'all, LEN(hello y'all) - LEN('ll') - (FIND('ll', hello y'all)-1))
        //Get the sub string after the first occurrence of “ll“ which is "o y'all"
        

Step 4: LEFT('hello y'all', FIND('ll','hello y'all') 
          + RIGHT(hello y'all, LEN(hello y'all) - LEN('ll') - (FIND('ll', hello y'all)-1))
        //Catenate "he" and "o y'all", and then you get the final result "heo y'all"
        

In case you want to use regular expression instead of search string, You can use REGEX function FIND(REGEX('l{2}'), hello y'all') instead of FIND('ll', hello y'all') to get the position, but please note REGEX function is not available in object formula field.

If you want to replace the substring rather than remove it, just simple catenate your replacement in-between, for example, replace the first occurrence of “ll“ with “yy“, so you get “heyyo y’all“ by running the formula below

LEFT('hello y'all', FIND('ll','hello y'all') + 'yy' + RIGHT(hello y'all, LEN(hello y'all) - LEN('ll') - (FIND('ll', hello y'all)-1))

If you have any questions or suggestions, please feel free to contact me.