4

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

  • how are these last names stored currently in your org ? in one field for everything ? – Samuel De Rycke Nov 7 '13 at 12:16
  • i m using the standard fields First Name and Last Name , and its the user who fill up the fields – Tomas Q. Nov 7 '13 at 12:20
  • So are you looking to grab the first and last 'last names' from the last name field only? In your example there are 4 last names. Will you always be using the first and last from this field in the formula field? – Chris Duncombe Nov 7 '13 at 12:24
  • I would like to grab always the name in the fiel "First Name" and from the field "Last Name" i want always the firts and the last names in this field(like in the example, i only want ferraz and montenegro) but in this field it can appear 2 , 3 , 5 .... "last names" but i only want the nº1 and the last – Tomas Q. Nov 7 '13 at 12:30
  • 1
    @PhilR The thing here, is that i need this name with this size, because in the future this "short name" it will be needed to print card an give to the different clients. Probably there is no one with a first name with 27 caracters. so far a have this solution FirstName & " " &LEFT(LastName,FIND(" ",LastName,1)-1) &RIGHT(LastName,FIND(" ",LastName,1)-1) and now i need to make sure that the name doesn t have more than 27 characters(space is counting has one) and if it has more than 27c i need to short the firts name of the last name field. – Tomas Q. Nov 7 '13 at 15:00
6

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

  • Nice answer !I will work on same and see if something can be done better :) but thanks for your time here – Mohith Shrivastava Nov 7 '13 at 14:42
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

FirstName+" "+LEFT(LastName,(FIND(" ",LastName)))+" "+RIGHT(LastName,(FIND(" ",LastName)))

enter image description here

  • 1
    This is not a correct formula. If the Name is for example Ferraz Amorim Silverio Montenegro then this formula gives Ferraz tenegro. This is because the FIND function searches always from left to right. – Sergej Utko Nov 7 '13 at 14:14
  • Agreed @mast0r .But this should give an idea to start thinking along same lines .I am busy but will correct this for sure – Mohith Shrivastava Nov 7 '13 at 14:21
  • Agree with you in this case ;) – Sergej Utko Nov 7 '13 at 14:22
  • Thank´s @MohithKumar i tried this formula , and it´s not working perfectly, but it can give me some idea. – Tomas Q. Nov 7 '13 at 14:29
  • @TomasQ. We will figure this up with exact formula ,no worries .I am busy will post solution if i get free time to work on this – Mohith Shrivastava Nov 7 '13 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.