Can anyone help with a formula to pull an email address from a text string in a standard field?

Question

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.

Answer 1

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;
            }
        }
    }
}

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)