Apex Code Development You need to sign in to do that
Don't have an account?
Serena Schultz Formula to extract data between two characters
Hi All,
I'm stuck. I need a field update formula to extract data from between two characters (in this case commas).
For Example: 00442178,01,1625196411116,JOSE,OMAR,MEJIA,SANCHEZ,214,
I want to extract the value between the 3rd and 4th comma. The number of commas will be consistent but not the number of characters between them.
I’ve been able to find the first comma using FIND(“,”, Field__c, 1), but I can’t figure out how to start at the third comma and return all values between it and the fourth comma. Help?
Serena
I'm stuck. I need a field update formula to extract data from between two characters (in this case commas).
For Example: 00442178,01,1625196411116,JOSE,OMAR,MEJIA,SANCHEZ,214,
I want to extract the value between the 3rd and 4th comma. The number of commas will be consistent but not the number of characters between them.
I’ve been able to find the first comma using FIND(“,”, Field__c, 1), but I can’t figure out how to start at the third comma and return all values between it and the fourth comma. Help?
Serena
It is possible theorically but the formula will be huge and you want perhaps the fifth field and other fields too.
https://developer.salesforce.com/forums/ForumsMain?id=9060G000000I1b2QAC
The best option is too avoid this kind of CSV field at the creation time splitting it in an object with many text fields.
For the fun, I will try to write a solution tomorrow and other people will also give you a solution here probably but the result will be quite ridiculous and more the field has a higher position (fifth, sixth field and so on) more the formula is complex (it lacks the split method or a regular expression extractor). There is a limit for the compiled formula and that could be impossible technically if the formula becomes too complex.
Best regards
Alain
I actually got a fix for this that isn't too bad. You are correct that I did need values between other commas as well.
The formula I ended up with parses the First Name, Second Name, Last Name, and Second Last Name values out of a Government ID Scan Buffer field and populates them into the Name field, also removing commas. Here is the formula that worked for me. Hopefully it helps others!
Serena
SUBSTITUTE(
MID(
Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c)+1)+1)+1,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c)+1)+1)+1)+1)+1)+1)
- (FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c,
FIND(",", Government_ID_Scan_Buffer__c)+1)+1)+1)
), ",", " ")
Each FIND is about 60 characters when saved and each new field needs three new FIND.
Some CSV files have hundreds of fields and the maximum formula size when saved is 5,000 bytes so up to 20-30 fields this formula can be saved.
https://resources.docs.salesforce.com/204/latest/en-us/sfdc/pdf/salesforce_formula_size_tipsheet.pdf
Your CSV field is simple. The complete CSV format allows commas inside the fields if they are enclosed with quotes, etc.
MID(
Custom_field__c,
FIND("_", Custom_field__c, FIND("_", Custom_field__c, FIND("_", Custom_field__c)+1)+1)+1,
FIND("_", Custom_field__c, FIND("_", Custom_field__c, FIND("_", Custom_field__c, FIND("_", Custom_field__c)+1)+1)+1) - (FIND("_", Custom_field__c, FIND("_", Custom_field__c, FIND("_", Custom_field__c)+1)+1)+1)
)