Extract Email Address (gmail)

[Victor_Grant]

I’m trying to extract emails (name@gmail.com)from a notes field.

The format is uniform “name@gmail.com”

But I can’t figure out a fomula to extract them

There is a “space” before the name and "space after the “.com”

[Matthew_Moran]

Will there be cases where there are multiple gmail addresses in a given notes field? Or will it be a single gmail address in a given notes field?

[Victor_Grant]

a single email address in a field of text

[Bill_French]

Find @ and parse left to the space and right to the space.

[Victor_Grant]

exactly! I’ve tried so many ways to write the formula but nothing works

[Bill_French]

Like this?

[Victor_Grant]

Exactly like that. Bill. you even pulled the emails that were not “gmail” perfect

[Bill_French]

Great - this is ugly because Airtable has so few tools to work with. It’s like starting a fire with wet hay and a couple of non-ferrous rocks in a 40mph crosswind.

But, it can be done despite the lack of simple string handling tools such as Split().

The formula to do this with Split is -

{Notes}.Split('@')[0].Split(' ')[1].Split(' ')[0]

Without Split() -

CONCATENATE(
    REPLACE(LEFT({Notes}, FIND("@", {Notes})),1,FIND("~",SUBSTITUTE(LEFT({Notes}, FIND("@", {Notes}))," ","~",LEN(LEFT({Notes}, FIND("@", {Notes}))) - LEN(SUBSTITUTE(LEFT({Notes}, FIND("@", {Notes}))," ","")))),""), 
    LEFT(RIGHT({Notes}, LEN({Notes}) - FIND("@", {Notes})), FIND(" ", RIGHT({Notes}, LEN({Notes}) - FIND("@", {Notes}))) - 1)
)

[sigh]

Don’t ask how I know how to do this. Just enjoy…

[Victor_Grant]

Thank you very much! I will enjoy and try to reverse engineer it to try to find out how you pull it off.

[Bill_French]

Victor_Grant:

… reverse engineer it to try to find out how you pull it off.

Do so at risk to your sanity. :winking_face: