From time to time as an Administrator you’ll need to take a string of text and parse out a few things so that you can use the information elsewhere.
The other day I was working with a client that required me to take a single Latitude / Longitude coordinate and separate the string into individual Latitude and Longitude fields so it could be used in a geocoding app.
The challenge was that that even though the latitude / longitude coordinate was separated by a comma, I could not rely on a fixed number of characters to make the separation. For example, a coordinate could be entered as 29.71028,-97.269452 or 34.858,-100.403.
To solve for this, I used the native formula wizard in Salesforce and leveraged the LEFT, FIND, RIGHT, and LEN functions.
The formula I used to pull out the Latitude coordinate (left of the comma) was:
LEFT(Combined_Lat_Long__c, (FIND(“,”, Combined_Lat_Long__c)-1))
So what is this formula doing exactly? It starts with the FIND function. The FIND function is looking for the comma in the text string and returns the number of characters into the text string when it hit the comma. Since I only want the characters left of the comma (not including the comma), I subtract “1”. Now I have the number of characters left of the comma. I used the LEFT function applied against my text string “Combined_Lat_Lon__c” to pull out the correct number of characters (courtesy of the FIND function).
The formula I used to pull out the Longitude coordinate (right of the comma) was:
RIGHT(Combined_Lat_Long__c, (LEN(Combined_Lat_Long__c) – FIND(“,”, Combined_Lat_Long__c)))
In this formula I’m building off my previous example. I’m using the LEN function to determine the total number of characters in my “Combined_Lat_Long__c” text string, and subtracting the number of characters up to the comma using the FIND function. That result is the number of characters I need to trim from the far right of the text string to pull out the Longitude, and the RIGHT function does this for us.