Poorly formatted data is a pain to deal with. In most cases, there isn't much you can do about it. However, in some areas, such as URL's, there is and we will explore an example of how to do this leveraging Salesforce's RIGHT and LEFT formula functions.
Full credit for the inspiration for this post goes out to Sunil Sarilla, Salesforce MVP and Answers Community Champion.
Inspiration Post: https://success.salesforce.com/answers?feedtype=RECENT&criteria=BESTANSWERS#!/feedtype=SINGLE_QUESTION_DETAIL&dc=All&criteria=BESTANSWERS&id=9063A000000pVAsQAM
Poorly formatted data causes errors, lost time, and frustration among your users. However, validation rules aren't always the best bet to force good data as you sometimes want to capture the data as it comes in such as in a web-to-lead form or via mass upload. Another good option is to have Process Builder or Workflow manage the data cleanup for you.
Take a look at an example of poor data; the Website field on the Account object is of DataType URL. But if this field has a strange character at the end ... This doesn’t look like the quality of data we would like to have!
The Standard Website fields don’t have the same custom URL field validations on them and are essentially just text fields for all intents and purposes. This means that it accepts poorly or even incorrectly formatted URL’s. No matter how Awesome you are, bad data will always creep up on you and here’s one area where you can do something about it.
Suggested Solution:
Let’s design a Workflow Rule that detects similar data on that field and adjust it.
Here is a walkthrough:
1- Create a Workflow Rule on the account object and choose the Evaluation Criteria to be “created, and any time it’s created to subsequently meet criteria”.
2- Set the Rule Criteria as “formula evaluates to true”. Then add the following formula :
RIGHT(Website,1)='/'
3- After saving, choose the Immediate Workflow Actions to be “New Field Update”
4- Add the Field Update, Choose the ‘Website’ field as the field to update and then add the formula that will correct the Data :
LEFT(
Website, LEN(Website)-1
)
Finally, Save and activate the Workflow Rule. This will definitely prevent similar future errors. As you notice other invalid data to clean up you can add them to the criteria in the workflow rule. Likewise, you can use the RIGHT function to capture cleanup details on the right side of the URL.
