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
Troy PlainTroy Plain 

SUBSTITUTION forumla to replace mutiple values

Hi guys, I'm trying to create a new field that take the information from another field and removes certain words. The SUBSTITUTION funtion works fine for one word, however there's a few different values that I want to remove.

This formula works fine for one value:
SUBSTITUTE(Site_Name__c, "Site ", "")

The Site Name field has been set up by multiple people and has become rather messy, so the same information has been entered in multiple ways. As such I need to be able to remove entries similar to the following:

"Site "
"SITE "
"Site Name "

I've tried mutiple variations of the SUBSTITUTE function (see below for examples) but cannot get the formula to look for multiple values and replace them with the same thing. Does anyone have any idea what I might be able to use to get the desired result?

Formulas I've tried and failed with:
OR
(SUBSTITUTE(Site_Name__c, "Site ", ""),
SUBSTITUTE(Site_Name__c, "SITE ", ""),
SUBSTITUTE(Site_Name__c, "Site Name", ""))))

SUBSTITUTE(Site_Name__c, "Site ", "")&SUBSTITUTE(Site_Name__c, "Site ", "")&SUBSTITUTE(Site_Name__c, "Site Name ", "")

SUBSTITUTE(Site_Name__c, "Site ", ""),(Site_Name__c, "SITE ", ""),(Site_Name__c, "Site Name ", "")






 
Best Answer chosen by Troy Plain
Troy PlainTroy Plain
If anyone else comes across this on their search for a similar formula this is what I eventually found as a solution:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Site_Name__c ,"Site ", ""),"SITE ", ""),"Site Name ","")

Thanks to Manoj for his assistance!

All Answers

ManojjenaManojjena
Hi Troy ,

Please check with below code it will help . 
if(Site_Name__c = "Site",SUBSTITUTE(Site_Name__c, "Site ", ""),IF(Site_Name__c = "SITE",SUBSTITUTE(Site_Name__c, "SITE ", ""),IF(Site_Name__c = "Site Name",SUBSTITUTE(Site_Name__c, "Site Name ", ""),Site_Name__c)))

Thnaks 
Manoj
Troy PlainTroy Plain
Hi Manoj,

Thank you for your reply there. It kinda works but sadly I didn't explain myself well enough in the opening post.

The Site Name field will have lots of different text in it that needs to be left over after the values I've listed above are removed. Your formula seems to be based on if the Site Name = the exact site name listed, when it should just contain that value a remove it accordingly. With that in mind I thought that maybe the formula should have been;

FIND(Site_Name__c = "Site",SUBSTITUTE(Site_Name__c, "Site ", ""),FIND(Site_Name__c = "SITE",SUBSTITUTE(Site_Name__c, "SITE ", ""),FIND(Site_Name__c = "Site Name",SUBSTITUTE(Site_Name__c, "Site Name ", ""),Site_Name__c)))

But that doesn't work either. Any further help would be appreciated!
Troy PlainTroy Plain
If anyone else comes across this on their search for a similar formula this is what I eventually found as a solution:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Site_Name__c ,"Site ", ""),"SITE ", ""),"Site Name ","")

Thanks to Manoj for his assistance!
This was selected as the best answer
Samet Atik 3Samet Atik 3
That was a brilliant answer, thank you so much.
MC34MC34
Hi, I used the same logic with the formula: 
  
SUBSTITUTE(SUBSTITUTE(Product_Name__c ,"S.", ""),"S-", "")

I am trying to extract the specific portion from the values from the text field (Product name) and putting them into formula field (Actual Product) 

User-added image

However, at the end of some values I have US.01 and the formula is also parsing the values and resulting in U01 which is not desirable. Is there a way to tweak the formula that it only does that at start of the values for "S." and "S-"  Not at the end? 

Thanks in advance
Mit
JaimeBidJaimeBid
What about using IF and CONTAINS?