Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
lodoss1118lodoss1118 

How to get last index of something in formula field

for example I have this string LONG-08080-aSDDdd-001

how do I get the end of the string after the last "-" : note that 001 could also be 00000001, 0100000 etc.. so not only 3 chars long

Steve :-/Steve :-/

I think you may need to use a FIND function, I don't have any examples handy, but there's one in the Formulas Guide Book posted in the online Learning Center
ImposterImposter

Hi lodoss1118,

I am facing the same issue.

Did you figure out a solution to retrieve the last index ?

 

Thanks,

 
Bernd WechnerBernd Wechner
Same quesiton here. No answer in 5 years. Boo, thumbs down Salesforce!
jieves@kyrobros.comjieves@kyrobros.com
Hopefully better late then never.  Had to figure this out today for a similar need.  I have a text field with 3 segments separated by underscore e.g. mySegment1_mySegment2_mySegment3.  I want a formula to return the third/last segment.

Simply replace myCustomField__c in the formula below with whatever field you want to return the last index.  NOTE: only works on fields with 3 segments.  NOTE: if you using some other separator besides underscore change out those 3 references as well.

TRIM(RIGHT(TRIM(RIGHT(myCustomField__c,LEN(myCustomField__c)-
FIND("_",myCustomField__c))),LEN(TRIM(RIGHT(myCustomField__c,LEN(myCustomField__c)-
FIND("_",myCustomField__c))))-FIND("_",TRIM(RIGHT(myCustomField__c,LEN(myCustomField__c)-
FIND("_",myCustomField__c))))))

My attempt to explain what is happening;

The base formula which will return everything after an underscore is
TRIM(RIGHT(myCustomField__c,LEN(myCustomField__c)-FIND("_",myCustomField__c)))

Since we want everything after the second underscore we replace all 3 occurences of the the custom field in the base formula with the formula itself.  Each occurrence of the base formula, which references the custom field, will return everything after the first underscore e.g. mySegment2_mySegment3.  The enclosing formula will then find everything after the first underscore of the mySegment1_mySegment2 string, which will be the third or last segment of the original string i.e. mySegment3.


 
Fran MejiasFran Mejias
You can use reverse to reverse the string, find the first occurrence, get the left until the first occurrence of the string and reverse again to get the original substring:

REVERSE(LEFT(REVERSE("your_field_or_string"), FIND("your_char_to_find", REVERSE("your_field_or_string"))-1))

Example: LONG-08080-aSDDdd-001

reverse("LONG-08080-aSDDdd-001") =  "100-ddDDSa-08080-GNOL"
find("-", "100-ddDDSa-08080-GNOL") = 4
left("100-ddDDSa-08080-GNOL", 4-1 (to remove the "-") = "100"
reverse("100") = "001"
 
Dharmendra Bhuva 8Dharmendra Bhuva 8
Solution given by Fran Mejias worked for me, thank you Fran!