So I get asked this quite often. How do I replace text in a formula field? Normally as a programmer I’d lean toward using a regular expression (or regex). Then I got to thinking… how would an administrator handle this? Because as a developer I have a whole set of tools at my disposal they don’t have..so I sat down and figured out HOW to handle it, and this is how I did it.
Consider the following problem : You have a text field (Company Name) that you need to use to create a unique file name. So you of course can append date/time and other variables to the field value to produce an unique name…BUT what if that name contains a special character? For Example : Wallace & Sons. What do you do now? The filename can’t contain the ampersand or “&” sign…
Enter the custom formula field. Formula fields let us create a value based on a formula applied against any number of fields both on the target object and any related objects. At first I decided to use the FIND() feature to determine if the text as present:
IF( FIND(Company_Name__c), "&") > 0, SUBSTITUTE(Company_Name__c, "&", ""), "")
Which works well for locating a single special character… and yes you can string them together with IF() statements and nest them… BUT this leads to issues in that you must account for both finding and NOT finding the search text for every character.
Upon closer inspection though, I noticed that SUBSTITUTE while it only accepts 3 parameters it will ALWAYS return itself as unaltered text if the search string is not found. So logically we can now nest the SUBSTITUTE function to find multiple characters. The following locates the ampersand or “&” sign and the percent or “%” sign:
SUBSTITUTE( SUBSTITUTE( Company_Name__c, "%", ""), "&", "" )
Therefore to create a more complex replacement is as simple as adding an outer SUBSTITUTE() function call. The following function removes :
dashes (-),
parenthesis (“),
spaces(” “),
ampersand (&)
period (.)
percent (%)
pound (#)
forward slash (/)
semi-colon (;)
colon(:)
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(Company__r.Name, "(", ""), ")", ""), " ", ""), "-", ""), "&", ""), ".", ""), "%", ""), "#", ""), "/", ""), ";", ""), ":", "")
And there you have it. The regular expression free, formula field way, to replace text in a field.
[contact-form]