function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
brkandivybrkandivy 

Substitute function in a field update formula

I am trying to do more than one substitution in a field update formula and am having difficulty getting it correct.   This is what I have:

 

OR(Contains(Name ,'Duplicate -'), SUBSTITUTE(Name,"Duplicate -"),

     (Contains(Name ,' - Duplicate'), SUBSTITUTE(Name," - Duplicate"),"")))

 

All help is greatly appreciated.

 

Thanks

 

 

Best Answer chosen by Admin (Salesforce Developers) 
brkandivybrkandivy

Sorry, this is the code I used where the first two lines work and not the third.

 

IF (  Contains( Name , 'Duplicate -'  ), SUBSTITUTE( Name,  "Duplicate -",  "" ) ,

IF (  Contains( Name , ' - Duplicate' ), SUBSTITUTE( Name,  " - Duplicate", "" ),

IF (  Contains( Name , 'CSD ' ), SUBSTITUTE( Name ,  "CSD ", "Community School District" ), Name )))

All Answers

SeAlVaSeAlVa

Check the code you pasted, you have more closing brackets than the ones you have opened.

 

Regards.
brkandivybrkandivy

I have spent several hours with this.  Unfortunately, when I take out the extra parentheses, I see an error to add them back in.  So, when I use this code:

 

OR(Contains(Name ,'Duplicate -'), SUBSTITUTE(Name,"Duplicate -"),    

(Contains(Name ,' - Duplicate'), SUBSTITUTE(Name," - Duplicate"),""))

 

I see this error:  Error: Syntax error.  Missing ')'  and it wants it placed after the second contains statement before the comma.  The comma highlights with the error.   So, if I add a parenthesis there, then I get a completely different error as below.

 

OR(Contains(Name ,'Duplicate -'), SUBSTITUTE(Name,"Duplicate -"),  (Contains(Name ,' - Duplicate')), SUBSTITUTE(Name," - Duplicate"),"")

 

Error:  Incorrect number of parameters for function 'SUBSTITUTE()'. Expected 3, received 2
SeAlVaSeAlVa

of course, because SUBSTITUE needs 

1. Text where you want to perform the replacement

2. Text you want to search

3. Text to replace what you have searched.

and you are only providing 'Name' as first parameter, and 'Duplicate' as second

 

Regards
brkandivybrkandivy

Thanks so much for your help, but its still not working.  I must not be understanding what you posted.

 

Here is what I have now:

 

OR (Contains(Name ,'Duplicate -'), SUBSTITUTE(Name,"Duplicate -"),"") ,      

      (Contains(Name ,' - Duplicate'), SUBSTITUTE(Name," - Duplicate"),"")

 

This is the error I see now:  Error: Syntax error. Extra ','

 

The comma is highlighted, so I take that out and get this error:  Error: Syntax error. Extra '('

 

Take that out and I see this error:  Error: Syntax error. Extra Contains

 

This is a vicious cycle that keeps going and basically strips away my second statement.

 

SFDC rreeeaaallly needs to make this simpler for those of us that are expected by our employers to be able to do this stuff when we are not programmers.
SeAlVaSeAlVa

what about...

 

IF (
  Contains(
    Name ,
    'Duplicate -'
  ), SUBSTITUTE(
    Name,
    "Duplicate -",
    ""
  ) ,IF (
    Contains(
      Name ,
      ' - Duplicate'
    ), SUBSTITUTE(
      Name,
      " - Duplicate",
      ""
    ), Name
  )
)

or

IF (
  Contains(
    Name ,
    'Duplicate -'
  ), SUBSTITUTE(
    Name,
    "Duplicate -",
    ""
  ), SUBSTITUTE(
    Name,
    " - Duplicate",
    ""
  )
)

 

 Regards

 

(I'm not sure if that is the behaviour you are looking for, but it is what looks like to me, if not, please explain the expected result)

 

 
brkandivybrkandivy

Thanks that worked. 

 

Now I have added a third line, and its saves with no errors, but the third line doesn't work.  So, I go to an account, and type - Duplicate after the account name, and that is removed as I expected.  If I type CSD in the Accound name, it doesn't get removed.  So the first two lines in the code work, but not the third.   I have about 50 scenarios to add to this, which is why I am trying to do this in one or two updates instead of 50.
brkandivybrkandivy

Sorry, this is the code I used where the first two lines work and not the third.

 

IF (  Contains( Name , 'Duplicate -'  ), SUBSTITUTE( Name,  "Duplicate -",  "" ) ,

IF (  Contains( Name , ' - Duplicate' ), SUBSTITUTE( Name,  " - Duplicate", "" ),

IF (  Contains( Name , 'CSD ' ), SUBSTITUTE( Name ,  "CSD ", "Community School District" ), Name )))

This was selected as the best answer
SeAlVaSeAlVa

I don't see why is not working. 

Have you tried to swap CSD with one of the Duplicate ones?

 

Have you checked that it actually has "CSD{blank space}" within the name field? (note that Salesforce cut out the last blank spaces, so unless you have more characters afterwards, white spaces will not be stored, making your third condition not to trigger.

 

Regards

 
brkandivybrkandivy

Yeah, I did try to remove the space and it still didn't work. 

 

However, I tried switching the order of the 2nd duplicate with the CSD and the CSD worked and not the duplicate.  So, my best guess now, is that you can only string two together.  Better than one by itself, but it would be nice to string more than two.

 

Thanks so much for your help!!  :smileyhappy:
Rom_Rom_
I had a similar issue. Putting SUBSTITUTE function with SUBSTITUTE function worked for me. This allows you to make as many substitutions for 1 field as needed:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Name,"CSD", "Community School District"),"Duplicate -","" ),"- Duplicate","")
Lizaveta MelnikavaLizaveta Melnikava
This is great , thank you Rom_