You need to sign in to do that
Don't have an account?
![Troy Plain Troy Plain](https://dfc-org-production.my.site.com/img/userprofile/default_profile_45_v2.png)
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 ", "")
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 ", "")
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Site_Name__c ,"Site ", ""),"SITE ", ""),"Site Name ","")
Thanks to Manoj for his assistance!
All Answers
Please check with below code it will help .
Thnaks
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!
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Site_Name__c ,"Site ", ""),"SITE ", ""),"Site Name ","")
Thanks to Manoj for his assistance!
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)
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