4

I am trying to pull an email address from a text string in the standard "Description" field on Case object.

The below formula is correctly pulling everything AFTER the "@" symbol in the email address, (for example "@sample.com").I cannot get it to pull the first part of the email address.

Here is the formula I am using

SUBSTITUTE( Description ,RIGHT(Description, FIND("@",Description))+ LEFT(Description, FIND("@",Description)), NULL)

Any advice is appreciated.

  • Is there a character that marks the beginning of the email string? If not, is there a fixed size to these emails? Or, is there a space before the email string begins? – battery.cord Oct 23 '14 at 13:17
5

You cannot do this via a formula field. You will have to do it with a trigger.

I tried to get this working with just Pattern and Matcher but was having issues. You can use this (it's pretty inefficient but it works)

trigger CaseTrigger on Case (before insert, before update) {
    for (Case c: Trigger.new) {
        if (c.Description != null) {
            Pattern emailPattern = Pattern.compile('(?i)^([A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,4})$');
            Boolean foundEmailMatch = false;

            for (String bit: c.Description.split(' ')) {
                Matcher emailMatcher = emailPattern.matcher(bit);
                if (emailMatcher.matches()) {
                    foundEmailMatch = true;
                    c.Description_Email__c = bit;
                }
            }

            if (!foundEmailMatch) {
                c.Description_Email__c = null;
            }
        }
    }
}
| improve this answer | |
2

It appears to me that the reason your formula doesn't work is because it's written in reverse. You have it written as RIGHT + LEFT when it needs to be written as LEFT + RIGHT. It worked for me when I essentially reversed what you had as follows:

SUBSTITUTE( Description ,LEFT(Description, FIND("@",Description)) + RIGHT(Description, FIND("@",Description)), NULL) 
| improve this answer | |

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.