[SalesForce] Formula field for getting a first and last word from the string

i need a help with a formula.
I want to create a formula that will select the firts name and the two last names and input them in a box. ps: i m portuguese and we have a lot of family names thats why i need this
Imagine:

First Name: Tomas
Last Name: ferraz amorim silverio montenegro

And the result like this Tomas Ferraz Montenegro

And the name can t excess a max of 27 caracters

Thank you very much

Best Answer

I think you should take a look at Salesforce's Formula Operators and Functions, and especially at the text Functions. I am not even sure it will be possible in you case but you can try it anyways:

FIND: Returns the position of a string within a string of text represented as a number.

LEFT: Returns the specified number of characters from the beginning of a text string.

RIGHT: Returns the specified number of characters from the beginning of a text string.

The only formula i can imagine for your case compares the trimmed and untrimmed name string to get the number of the words in the whole string. And then search for the last blank. Unfortunately we can't create a full dynamic formula (i can't find a way to search the string from the right side), in my example this will work with up to 7 words in the name string (you can try to expand this formula):

First_Name__c & ' ' & LEFT(Last_Name__c, FIND(" ", Last_Name__c)) & ' ' &
CASE( LEN(Last_Name__c) - LEN(SUBSTITUTE(Last_Name__c,' ', '')),
1, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c)),
2, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)),
3, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1) ),
4, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1) ),
5, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1)+1) ),
6, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1)+1)+1) ),
Last_Name__c
)

This is the result:

enter image description here

Related Topic