[SalesForce] Formula to cut word from a text string

Hi I have a field as 'Category' which can take string separated by comma (,) e.g as below

Category = Exhibition_Sport and Leisure, Family, Family_Sport and Leisure, Sport

I am trying to cut the last word from Right i.e 'Sport' into custom field 'Vertical' and the rest of the text into another custom field 'Event Type'.

Also the above string can have either one or more than one word separated by comma. If the string has only one word say 'Sport' it should be updated to 'Vertical'

I/p  Category = Exhibition_Sport and Leisure, Family, Family_Sport and Leisure, Sport


o/p Vertical = Sport

Event Type = Exhibition_Sport and Leisure, Family, Family_Sport and Leisure

What is the best way to implement the formulae?

Best Answer

You need to have a good understand of formulas for this to work. Specifically, IF(), BEGINS(), RIGHT(), LEFT(), TRIM(), FIND(), and CONTAINS().

The following should be able to set your Vertical__c. It will check the Category__c starting from the right and proceed to increment the string's length by 1 until that string begins with a ",". Once it does, it also verifies that the string does not contain any other apostraphes. If it does not, that is the last unique String in the list.

IF(
    BEGINS(
        RIGHT(Category__c, 1), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 0), ","
    ),
    TRIM(RIGHT(Category__c, 0))
)
IF(
    BEGINS(
        RIGHT(Category__c, 2), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 1), ","
    ),
    TRIM(RIGHT(Category__c, 1))
)
IF(
    BEGINS(
        RIGHT(Category__c, 3), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 2), ","
    ),
    TRIM(RIGHT(Category__c, 2))
)
IF(
    BEGINS(
        RIGHT(Category__c, 4), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 3), ","
    ),
    TRIM(RIGHT(Category__c, 3))
)
IF(
    BEGINS(
        RIGHT(Category__c, 5), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 4), ","
    ),
    TRIM(RIGHT(Category__c, 4))
)
IF(
    BEGINS(
        RIGHT(Category__c, 6), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 5), ","
    ),
    TRIM(RIGHT(Category__c, 5))
)
IF(
    BEGINS(
        RIGHT(Category__c, 7), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 6), ","
    ),
    TRIM(RIGHT(Category__c, 6))
)
IF(
    BEGINS(
        RIGHT(Category__c, 8), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 7), ","
    ),
    TRIM(RIGHT(Category__c, 7))
)
IF(
    BEGINS(
        RIGHT(Category__c, 9), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 8), ","
    ),
    TRIM(RIGHT(Category__c, 8))
)
IF(
    BEGINS(
        RIGHT(Category__c, 10), ","
    )
    &&
    !CONTAINS(
        RIGHT(Category__c, 9), ","
    ),
    TRIM(RIGHT(Category__c, 9))
)

You can expand this count for a bit to check more characters to the right of the end until you hit the maximum character limit of 5000. This formula is currently around 1800 characters. You should probably be able to get up to 20 characters checked easily.

You should then be able to utilize a function similar to what Michael detailed out to get the Event_Type__c. It is important to note that you must not utilize the full 5000 characters in Vertical__c to ensure it can still be used in this formula.

LEFT(
    Category__c, 
    FIND(", " + Vertical__c, Category__c)
)

This will take all characters to the left of the Vertical__c in Category__c. Note that the ", " is prepended to the beginning of the Vertical__c to ensure they are removed from the Event_Type__c.

Note: I have not been able to test this myself, but this should give you a good starting point. Good luck!

Related Topic