Formula to cut word from a text string

Question

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?

Answer 1

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!

Answer 2

Unfortunately, you cannot achieve a fully dynamical solution, as the field formulas do not feature any function which might find last occurrence of a string/character. If there existed such a function, you might easily accomplish what you are looking for with a formula which would resemble something like this:

IF(
    CONTAINS(Category__c, ","),
    RIGHT(Category__c, LEN(Category__c)-FINDLAST(",")),
    Category__c
)

However, as no such function exists (FINDLAST()), but rather only FIND() function, you might resort to a 'dirty fix' solution, which would handle each case specifically, for every number of commas occurring in your Category__c field. I.e. for each increasing number of comma occurences, you should move the starting index (third param) of the main FIND() function a bit further to the right:

CASE(
    LEN(Category__c) - LEN(SUBSTITUTE(Category__c, ',', '')),   // number of commas in Category__c field
    1, RIGHT(Category__c, LEN(Category__c) - FIND(",", Category__c)),
    2, RIGHT(Category__c, LEN(Category__c) - FIND(",", Category__c, FIND(",", Category__c))),
    3, RIGHT(Category__c, LEN(Category__c) - FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c))) ),
    4, RIGHT(Category__c, LEN(Category__c) - FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c)))) ),
    5, RIGHT(Category__c, LEN(Category__c) - FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c, FIND(",", Category__c))))) ),
    Category__c     // default
)

Please have in mind that this is a dirty fix and the fiven example might not work propperly should you have more than 5 commas occurring in the Category__c field. Still, you might add additional cases for each additional comma should you be able to approximate this number at all.

Answer 3

This worked for me to remove last word of string, based on Jesse Altman's string. Sub "Street" for "Category__c"

IF(
    (BEGINS(
        RIGHT( Street , 1), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 0), " "
    )),
     LEFT( Street ,LEN( Street ) -  0)
,
 IF(
    (BEGINS(
        RIGHT( Street , 2), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 1), " "
    )),
     LEFT( Street ,LEN( Street ) - 1)
,
 IF(
    (BEGINS(
        RIGHT( Street , 3), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 2), " "
    )),
     LEFT( Street ,LEN( Street ) - 2)
,
 IF(
    (BEGINS(
        RIGHT( Street , 4), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 3), " "
    )),
     LEFT( Street ,LEN( Street ) - 3)
,
 IF(
    (BEGINS(
        RIGHT( Street , 5), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 4), " "
    )),
     LEFT( Street ,LEN( Street ) - 4)
,
 IF(
    (BEGINS(
        RIGHT( Street , 6), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 5), " "
    )),
     LEFT( Street ,LEN( Street ) - 5)
,
 IF((
    BEGINS(
        RIGHT( Street , 7), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 6), " "
    )),
     LEFT( Street ,LEN( Street ) - 6)
,
 IF((
    BEGINS(
        RIGHT( Street , 8), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 7), " "
    )),
     LEFT( Street ,LEN( Street ) - 7)
,
 IF((
    BEGINS(
        RIGHT( Street , 9), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 8), " "
    )),
     LEFT( Street ,LEN( Street ) - 8)
, 
IF((
    BEGINS(
        RIGHT( Street , 10), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 9), " "
    )),
     LEFT( Street ,LEN( Street ) - 9)
, 
IF((
    BEGINS(
        RIGHT( Street , 11), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 10), " "
    )),
     LEFT( Street ,LEN( Street ) - 10)
, 
IF((
    BEGINS(
        RIGHT( Street , 12), " "
    )
    &&
    !CONTAINS(
        RIGHT(Street, 11), " "
    )),
     LEFT( Street ,LEN( Street ) - 11)
, Street))))))))))))

Answer 4

For o/p Vertical = Sport Use Formula:REVERSE(LEFT(REVERSE({!Reseller_name}), FIND(",",REVERSE({!Reseller_name})) - 1)).

To get below result:

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

Use Formula: REVERSE( TRIM(RIGHT(REVERSE({!Reseller_name}) , LEN(REVERSE({!Reseller_name}) ) - FIND(",",REVERSE({!Reseller_name}) ))))