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

Question

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

Answer 1

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:

Answer 2

Assuming there is a space always between different last names ,the below formula can be used to start with

any parsing of string in formula field can be done via FIND ,LEFT,RIGHT,LEN functions

Here is the partial correct formula for your requirement ,although last part with right function is not correct as FIND takes first space ,we need to find an algorithm to just get the right most characters before space,but something to help you start

OLD VERSION==> FirstName+" "+LEFT(LastName,(FIND(" ",LastName)))+" "+RIGHT(LastName,(FIND(" ",LastName))) CORRECTED VERSION==> FirstName+" "+LEFT(LastName,(FIND(" ",LastName)))+" "+RIGHT(LastName,(FIND(" ",REVERSE(LastName))))