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
MC34MC34 

Parsing specific texts after "."?

Hi,

I am  trying to extract text after the last "." used in the value for the text field (External_Product_Name__c) and putting them into formula field (Pure Product Name__c) 

So I want to take the text out of this: 

SuP.53356.BLACK/BLACK INK.Chrome cool Sleek stylus pen

To this: 

Chrome cool Sleek stylus pen

I want to take the last "." into consideration not first or second. 

Last time I used something like that for other issue: 
  
IF(left(Product_Name__c,2) ='S-',MID(Product_Name__c, 3, LEN(Product_Name__c)),
IF(left(Product_Name__c,2) ='RSMPL.',MID(Product_Name__c, 7, LEN(Product_Name__c)),

Product_Name__c)))

However, this is purely based on position of last "." in the text.  How can i take last "." into account while building the formula? 

Thank you, 
Mit

 
Best Answer chosen by MC34
Eric Praud 8Eric Praud 8
Ok, I've found a workaround that should work for you as per this:
https://trailblazers.salesforce.com/answers?id=9063A000000ZlIzQAK

SUBSTITUTE(RIGHT(SUBSTITUTE(Product_Name__c,".","~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"),50),"~","")

Basically, we'replacing the dots with 50 "~" so to push the last bit to the right as much as possible. Then we take the last 50 characters and remove the "~". It's a bit of a hack, but I assume you'll never have more than 50 characters after the last dot. If you do, just add as manay of those ~ as needed, and change the number 50 accordingly
 

All Answers

AbhishekAbhishek (Salesforce Developers) 
https://trailblazers.salesforce.com/answers?id=9064S000000DDoLQAW

Try it for suggestions.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks.
Eric Praud 8Eric Praud 8
@Abhishek, that is @Fincher's last question, and it doesn't answer this specific question, it's the exact opposite.
@Fincher, I'm trying to find a way, but you may need some code here, since FIND only works left to right, and we never know how many dots you have in your string. I'm trying to come up with something, but not sure I'll get there with a simple formula
MC34MC34
Sorry guys - I should have used the same name as in Salesforce Trailblazer communities. :(
MC34MC34
@Eric - I cannot work any formula that takes into the

last dot from the right  - extract text after that 

or

first dot from the left? - extract text before that 

Just wondering. :)
 
Eric Praud 8Eric Praud 8
Ok, I've found a workaround that should work for you as per this:
https://trailblazers.salesforce.com/answers?id=9063A000000ZlIzQAK

SUBSTITUTE(RIGHT(SUBSTITUTE(Product_Name__c,".","~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"),50),"~","")

Basically, we'replacing the dots with 50 "~" so to push the last bit to the right as much as possible. Then we take the last 50 characters and remove the "~". It's a bit of a hack, but I assume you'll never have more than 50 characters after the last dot. If you do, just add as manay of those ~ as needed, and change the number 50 accordingly
 
This was selected as the best answer
Eric Praud 8Eric Praud 8

@Fincher, just saw your comment about the textbefore the first dot. That's the easy one, go with:

LEFT(Product_Name__c,FIND(".",Product_Name__c)-1)
MC34MC34
@Eric. No I mean

Last dot from the right  - Keep text whatever comes after that. 

or

first dot from the left? - Keep text whatever comes before that. 

(Either one should do) 

Like in this one : SuP.53356.BLACK/BLACK INK.Chrome cool Sleek stylus pen
 
Eric Praud 8Eric Praud 8
Not too sure what you're asking now...
LEFT(Product_Name__c,FIND(".",Product_Name__c)-1)
Will give you "SuP"

SUBSTITUTE(RIGHT(SUBSTITUTE(Product_Name__c,".","~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"),50),"~","")
Will give you "Chrome cool Sleek stylus pen"
 
MC34MC34
Eric, this was good solution: 

SUBSTITUTE(RIGHT(SUBSTITUTE(Product_Name__c,".","~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"),50),"~","")

Only problem is that dots are other places too. But 90% it working where dots are at correct place to cut the string. 
Sorry it cannot get better than that. I am pitching to make it more streamlines from the data-source side now. 

Thanks. 

 
Eric Praud 8Eric Praud 8
I don't understand here when you write "Only problem is that dots are other places too". I thought you wanted the text after the last dot?
When there is more than one in the Product name, it works, only the latest text is returned (I tested). Am I missing something?
MC34MC34
@Eric, My apologies..if that sentenece caused the confuision. Actually what I am finding out is - that Business has these Product Names that are flowing through Oracle System to Salesforce. The problem is that 90% times I can parse when taking the last dot into account. However, 10% times Product names are like:
  • 1002.kiu45.78.yt77.Black.Nozzletip Water Bottle
  • 67562.t88.833.Silk.Autumn Cozy Scarf 

Where you can see that position of the dot is not exactly before the full product name.  So, in the above example Black.Nozzletip Water Bottle and Silk.Autumn Cozy Scarf is getting parsed as Nozzletip Water Bottle and Autumn Cozy Scarf

So the formula works and is awesome. Its too bad that I cannot rely on the parsing formula in this case. :( 
Maybe if the full naming standardization is reached from the source system.