I would like to split the username and email domain from an email address. For Example, test.leo@gmail.com to test.leo and **gmail ** without .com Formula used: =IFERROR(SPLIT(REGEXREPLACE(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)), "\[a-zA-Z0-9_]",""), "@"), "")

Reputation: 1

Split email address by username and email domain

I would like to split the username and email domain from an email address. For Example, [email protected] to test.leo and **gmail ** without .com

Formula used: =IFERROR(SPLIT(REGEXREPLACE(INDEX(Filter!$A:$AY,MATCH($A39,Filter!$A:$A,0),MATCH(N$1,Filter!$1:$1,0)), "\[a-zA-Z0-9_]",""), "@"), "")

Upvotes: 0

Reputation: 30309

tested on couple of email address varieties here. you may test it out extensively on your whole data set & see how it goes...

=INDEX(IF(LEN(A2:A),REGEXREPLACE(REGEXEXTRACT(A2:A,"^(.*)@(.*)\."),"\.co$",""),))

Upvotes: 1

Reputation: 10277

You can try also with this option:

=INDEX(IF(ISEMAIL(A2:A),REGEXEXTRACT(A2:A,"(.+)@(.+?)."),))

If you need only to remove the last ".***" Just remove the interrogation mark

Upvotes: 0