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
Tasia Demuth 4Tasia Demuth 4 

Do any of you know of a formula that will grab the emails from a format like this: John <john@esource.com>, Nick <nick@esource.com>

I am looking for a way to copy the emails from a calendar event and returning just the email string, rather than the name and email string. 

Ideally it would look like: john@esource.com,nick@esource.com

I have been playing around with the TRIM and SUBSTITUTE functions but have been struggling to get it just right. 

Any help would be greatly appreciated. 

Thanks!
Tasia
SarvaniSarvani
Hi Tasia,

Please refer below sample where its extracts only emil from part from give string. Though I am not sure where exectly you are using this might help 
string x='John <john@esource.com>';
Integer startchar = x.indexOf('<',0);
integer endchar=x.indexOf('>',0);
string email=x.substring(startchar,endchar);
System.debug('Emails is :'+email);
Hope this helps! Please mark as best if it does

Thanks
Tasia Demuth 4Tasia Demuth 4
Oh that is very helpful! I will play around with it and see what I can do! Thanks, Tasia
Alain CabonAlain Cabon
With a formula, it is very difficult because each new email in the list increases the calculation of the offset ( positions just beyond the next "<" and ">" ).

First email and found first "<" ( no offset for the FIND )

Second email and found second "<" needs an offset ( FIND( '<',email__c)+1 ) repeated in each FIND ( searched text, source text , offset )   
  
MID(email__c, FIND( '<',email__c)+1, FIND('>',email__c)-FIND('<',email__c )-1)
+ ', ' +
MID(email__c, FIND( '<',email__c, FIND( '<',email__c)+1)+1,
FIND('>',email__c, FIND( '>',email__c)+1)-FIND('<',email__c,FIND( '<',email__c)+1 )-1)

User-added image

The formula works for just two emails (very restricted use). Apex is a better option like above but you also need a trigger.
Tasia Demuth 4Tasia Demuth 4
Thanks, Alain! You are always so helpful. If it only works for two emails, then I agree Apex seems to be the better option. I will keep investigating and playing with this. It is kinda a fun but challenging puzzle. Thanks! Tasia
Alain CabonAlain Cabon
Hi Tasia,

More emails could be found with a formula but since the third email the formula becomes very complicated and for the fourth or fifth email that could already exceed the compiled maximum size permitted for a formula so it is not an interesting option excepted if you have always one or two emails.

In Apex, you can get hundreds of emails (possible for a mailing list) because there are the split method and methods for the regular expressions that are the main missing methods for the formula field.

As soon as we could write at least in a  formula: SPLIT( email__c, "," , 1 ) for getting the first email and SPLIT( email__c, "," , 2 ) for the second one, etc, the problem will become a easier task but that is still missing.

 
Alain CabonAlain Cabon
With Apex, you can also regular expressions with capturing groups.

String regExp = '<(.+?)>';   //  between the parenthesis, there is the first capturing group (1).

.+?  : means any characters (the period) (at least one (+)) after ( < ) up to the character after the '?'  here ( > ) (the parenthesis are not used) 
  
String emails =  'John <john@esource.com>,Nick <nick@esource.com>,   Mary <mary@esource.com>';
String regExp = '<(.+?)>';
Matcher m = Pattern.compile(regExp).matcher(emails);
Set<String> semail = new Set<String>();
while (m.find()) {
    system.debug(m.group(1));
    semail.add(m.group(1).trim());  // trim useless most of the time
}
List<String> lt = new List<String>();
lt.addAll(semail);
String st = String.join(lt,', ');
system.debug('reformatted emails: ' + st);
Tasia Demuth 4Tasia Demuth 4
Oh interesting. That could be useful for this use case. Would this allow for up to 100 emails to be grouped and trimmed?
Alain CabonAlain Cabon
There is no limit here because it is in Apex.

That is the real interest of the regular expressions indeed because there is an iterator ( m.find() ) that will browse all the emails that mach the format ( < anything between  > )
Tasia Demuth 4Tasia Demuth 4
Okay cool! That would probably be the best solution then. I will keep working on it. Thanks again! Tasia
Alain CabonAlain Cabon
Otherwise, there is the simple: String semail[] = emails.split(',');  and you browse each email of the array using the first apex solution for the reformatting. It is almost the same.
It is just to know an alternative but we use the two solutions alternately.
The problem of the regular expressions is to understand what they are doing exactly contrary to indexOf and substring.
The regular expressions are usefull for complicated extractions where the uses of indexOf and substring become many in a code with many tests.