function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Bivolaru OvidiuBivolaru Ovidiu 

Formula field to extract first 3 words

Hi,

 I'm trying to extract the first 3 words from a TEXT field that can have one word or more than 3 words. Is there a way to achieve this using LEFT, RIGHT, TRIM functions ? Thank you!
Best Answer chosen by Bivolaru Ovidiu
Alain CabonAlain Cabon
Finally, the shortest formula could be this one:
  
'[' + IF(FIND(' ',TRIM(test_text__c ))==0|| FIND(' ',TRIM(test_text__c),FIND(' ',TRIM(test_text__c),1)+1)==0,
 TRIM(test_text__c ),
 TRIM(LEFT(TRIM(test_text__c)+' ', FIND(' ',TRIM(test_text__c )+' ',
                         FIND(' ',TRIM(test_text__c )+' ',  
                         FIND(' ',TRIM(test_text__c )+' ',1)+1)+1)))) + ']'

Alain

All Answers

Bivolaru OvidiuBivolaru Ovidiu
And FIND function, of course.
Alain CabonAlain Cabon
Hello Bivolaru,

I like to resolve solve some tricky puzzles like that and the formulas of Salesforce without the '"split"  function impose ridiculous circumventions.

I wrote the next formula for your puzzle:
  
'[' + IF(FIND(' ',TRIM(test_text__c ))==0,TRIM(test_text__c ),
      IF(FIND(' ',TRIM(test_text__c),FIND(' ',TRIM(test_text__c),1)+1)==0,
 TRIM(LEFT(TRIM(test_text__c)+' ',FIND(' ',TRIM(test_text__c )+ ' ', FIND(' ',TRIM(test_text__c ), 1)+1))),
 TRIM(LEFT(TRIM(test_text__c)+' ', FIND(' ',TRIM(test_text__c )+' ',
                         FIND(' ',TRIM(test_text__c )+' ',  
                         FIND(' ',TRIM(test_text__c )+' ',1)+1)+1))))) + ']'

If there is one word, the result is one word.
If there are two words, the result is two words.
If there are three words or more, the result is three words.

A good puzzle for developer.

Best regards

Alain

 
Alain CabonAlain Cabon
Finally, the shortest formula could be this one:
  
'[' + IF(FIND(' ',TRIM(test_text__c ))==0|| FIND(' ',TRIM(test_text__c),FIND(' ',TRIM(test_text__c),1)+1)==0,
 TRIM(test_text__c ),
 TRIM(LEFT(TRIM(test_text__c)+' ', FIND(' ',TRIM(test_text__c )+' ',
                         FIND(' ',TRIM(test_text__c )+' ',  
                         FIND(' ',TRIM(test_text__c )+' ',1)+1)+1)))) + ']'

Alain
This was selected as the best answer
Saravanan Sivalingam 1Saravanan Sivalingam 1

You can use the MID() function.
Returns character from the middle of a text string, given a starting position and length

Its worked for me..I created same scenario and got the Substring as a "Name"

MID( YourfieldNameHere , 16, 5)

Syntax :  
MID(text, start_num, num_chars)

Thanks,
Saravanan Sivlingam.
Bivolaru OvidiuBivolaru Ovidiu
Alain, you're the man!!! Thank you! It works very well.