Extract company name from email address with multiple values after @

[candymycandy]

Hi,

I have a formula that extracts the domain/company name from an email address after the @ sign when the format is BobSmith@Company.com
=MID(LEFT(D748,FIND(".",D748,FIND("@",D748))-1),FIND("@",D748)+1,99)

Is it possible to do this to extract the domain/company when the email address is in the format BobSmith@UK.Company.com please?

Thanks in advance for any help!

 

[Sufiyan97]

Try

Book2
	A	B	C
1
2	BobSmith@Company.com	Company.com
3	BobSmith@UK.Company.com	UK.Company.com
4
5
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTAFTER(A2,"@")

 

[candymycandy]

Try

Book2
	A	B	C
1
2	BobSmith@Company.com	Company.com
3	BobSmith@UK.Company.com	UK.Company.com
4
5
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=TEXTAFTER(A2,"@")

Thanks for your reply, sorry I wasn't clear. I would still just like to see only the company name.

So from "BobSmith@UK.Company.com" I only want to return "Company". I have a few email addresses in this format, e.g. SarahSmith@eu.Companyname.com

I am not sure this is possible but any help greatly appreciated

 

[Fluff]

Can you also have domains like Company.co.uk

 

[Georgiboy]

Couple of options I have played with before:

1st Option is not very dynamic:

Book1
	A	B
1	Email	Company
2	BobSmith@Company1.com	Company1
3	BobSmith@UK.Company2.com	Company2
4	BobSmith@UK.Company123.co.uk	Company123
5	BobSmith@Company-951.UK.com	Company-951
6	BobSmith@eu.Company-951.UK.com	Company-951
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LET( data,TEXTAFTER(A2:A6,"@"), xml,"<t><s>"&SUBSTITUTE(data,".","</s><s>")&"</s></t>", f,IFERROR(FILTERXML(xml,"//s["&SEQUENCE(,10)&"]"),""), m,MAP(f,LAMBDA(x,IF(LEN(x)=2,"",IF(LOWER(x)="com","",x)))), BYROW(m,LAMBDA(x,FILTER(x,x<>""))))
Dynamic array formulas.

Second option lets you have a list where you can exclude parts of the email address by dropping the value to remove in column K and the pipe symbol in column L:

Book1
	E	F	G	H	I	J	K	L
1	Email	Company					Exclude list
2	BobSmith@Company1.com	Company1					com	|
3	BobSmith@UK.Company2.com	Company2					uk	|
4	BobSmith@UK.Company123.co.uk	Company123					co	|
5	BobSmith@Company-951.UK.com	Company-951					eu	|
6	BobSmith@eu.Company-951.UK.com	Company-951
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=LET(rng,A2:A6,f,K2:K26,r,L2:L26, BYROW(rng,LAMBDA(x,LET(t,DROP(TEXTSPLIT(x,{".","@"},,TRUE),,1),SUBSTITUTE(TEXTJOIN("",TRUE,IFNA(XLOOKUP(t,f,r),t)),"|","")))))
Dynamic array formulas.

 

[candymycandy]

Can you also have domains like Company.co.uk

Hi there, yes it will be any email address variation. I just have some which have these prefixes to denote the company branch location. Thanks!

 

[candymycandy]

Couple of options I have played with before:

1st Option is not very dynamic:

Book1
	A	B
1	Email	Company
2	BobSmith@Company1.com	Company1
3	BobSmith@UK.Company2.com	Company2
4	BobSmith@UK.Company123.co.uk	Company123
5	BobSmith@Company-951.UK.com	Company-951
6	BobSmith@eu.Company-951.UK.com	Company-951
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LET( data,TEXTAFTER(A2:A6,"@"), xml,"<t><s>"&SUBSTITUTE(data,".","</s><s>")&"</s></t>", f,IFERROR(FILTERXML(xml,"//s["&SEQUENCE(,10)&"]"),""), m,MAP(f,LAMBDA(x,IF(LEN(x)=2,"",IF(LOWER(x)="com","",x)))), BYROW(m,LAMBDA(x,FILTER(x,x<>""))))
Dynamic array formulas.

Second option lets you have a list where you can exclude parts of the email address by dropping the value to remove in column K and the pipe symbol in column L:

Book1
	E	F	G	H	I	J	K	L
1	Email	Company					Exclude list
2	BobSmith@Company1.com	Company1					com	|
3	BobSmith@UK.Company2.com	Company2					uk	|
4	BobSmith@UK.Company123.co.uk	Company123					co	|
5	BobSmith@Company-951.UK.com	Company-951					eu	|
6	BobSmith@eu.Company-951.UK.com	Company-951
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=LET(rng,A2:A6,f,K2:K26,r,L2:L26, BYROW(rng,LAMBDA(x,LET(t,DROP(TEXTSPLIT(x,{".","@"},,TRUE),,1),SUBSTITUTE(TEXTJOIN("",TRUE,IFNA(XLOOKUP(t,f,r),t)),"|","")))))
Dynamic array formulas.

thank you, I tried the first one which works but not for company names with only 2 characters. Also I cannot extend it to more than about 150 rows?

 

[Fluff]

How about

Fluff.xlsm
	A	B
1
2	BobSmith@Company.com	Company
3	BobSmith@UK.Company.com	Company
4	BobSmith@UK.Company.co.uk	Company
5	BobSmith@Company.co.uk	Company
6	BobSmith@ab.co.uk	ab
7	BobSmith@ab.com	com
Lists

Cell Formulas
Range	Formula
B2:B7	B2	=LET(s,TEXTSPLIT(TEXTAFTER(A2,"@"),"."),TAKE(FILTER(s,LEN(s)=MAX(LEN(s))),,1))

But you are going to have problems with companies that only have 2 letters.

 

[Georgiboy]

thank you, I tried the first one which works but not for company names with only 2 characters. Also I cannot extend it to more than about 150 rows?

The second option might be better for you anyway.

To change the range on both options you would need to edit the 'A2:A6' part

 

[candymycandy]

The second option might be better for you anyway.

To change the range on both options you would need to edit the 'A2:A6' part

Thank you, I had changed the range but it came back with a calc error? Thank you for taking the time to help!