Help

Extract Email Address (gmail)

4206 15
cancel
Showing results for 
Search instead for 
Did you mean: 
Victor_Grant
4 - Data Explorer
4 - Data Explorer

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”

15 Replies 15
Matthew_Moran
7 - App Architect
7 - App Architect

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?

a single email address in a field of text

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

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

Like this?

image

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

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…

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

Do so at risk to your sanity. :winking_face: