+ Start a Discussion
MC34MC34 

Parsing specific texts from the field value.

Hi,

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) 

The formula I am using this: 
SUBSTITUTE(SUBSTITUTE(Product_Name__c ,"S.", ""),"S-", "")
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 for start of the values for "S." and "S-"  Not at the end? 

Formula field                |  Text field
User-added image
Thank you
Best Answer chosen by MC34
Agustin BAgustin B
Hi Fincher, sorry for the delay:
Try this:
IF(Left(Product_Name__c,2) ='S.',MID(Product_Name__c, 3, LEN(Product_Name__c)), IF(left(Product_Name__c,2) ='S-',MID(Product_Name__c, 3, LEN(Product_Name__c)),Product_Name__c))

If it is not 3, try with 2 or 4.

If it helps please like and mark as correct as it may help others asking the same.
good luck

All Answers

Agustin BAgustin B
Hi Fincher, if it is always going to be the beggining what you need to replace then you could do something like this(this is not the actual formula you should write): IF(LEFT(ProductId,2) ='S.',Remove the first 2,IF(LEFT(ProductId,2) ='S-',Remove the first 2,Productid))

If it helps please like and mark as correct as it may help others. 
MC34MC34
Hi Agustin, 

If I use "Remove the first 2" criteria then it will also remove the first two characters from other values that do not start with "s." or "s-". for example some product names start with 19.... or numbers. 
MC34MC34
I tried this 
IF(Left(Product_Name__c,2) ='S.',Null, IF(left(Product_Name__c,2) ='S-',Null,Product_Name__c))
How can I use "remove the first 2" in the formula. Because the formula is not working it is only showing me null for all the product name with "s." or "s-" 

 
Agustin BAgustin B
Hi Fincher, sorry for the delay:
Try this:
IF(Left(Product_Name__c,2) ='S.',MID(Product_Name__c, 3, LEN(Product_Name__c)), IF(left(Product_Name__c,2) ='S-',MID(Product_Name__c, 3, LEN(Product_Name__c)),Product_Name__c))

If it is not 3, try with 2 or 4.

If it helps please like and mark as correct as it may help others asking the same.
good luck
This was selected as the best answer
MC34MC34
@
Agustin B - Can you please help me something regaerding this?