split user ID and domain name from email addresses using formula

excelismagic
excelismagic used Ask the Experts™
on
attached is the file with example data , column A is what i have and column B C D is what i need to get with formula.

any help is appreciated.
EE.xlsb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try
=TRIM(MID(SUBSTITUTE(C2,".",REPT(" ",LEN(C2))),1,LEN(C2)))
=TRIM(MID(SUBSTITUTE(A2,"@",REPT(" ",LEN(A2))),LEN(A2),LEN(A2)))
=TRIM(MID(SUBSTITUTE(A2,"@",REPT(" ",LEN(A2))),1,LEN(A2)))

Open in new window

Regards
EEv1.xlsb
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
For User ID, you can use below formula:
=LEFT(A2,(FIND("@",A2,1)-1))
For Domain with extention:
=MID(A2,FIND("@",A2)+1,256)
and for Domain without ext:
=MID(A2,FIND("@",A2)+1,FIND(".",A2,FIND("@",A2)+1)-FIND("@",A2)-1)
Please find attached for your reference:
EE_3-v1.xlsb

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial