Help in IF formula for multiple text

[chris1979]

I require assistance in a if (or any function). I have a report with different email domains but have a different name in the company columns

Sender_Email	COMPANY_NAME	Final
ABC@ABC.com	ABC
BCA@service.com	BCA
XYZ@data.com	BCA

I want a formula

1. where if the company in column "Company Name" is ABC then it should remain as ABC in column "Final "
2. where the company in column "Company Name" is BCA then check column "Sender_Email.....check the domain name and update the column "Final" as "Service" (if the domain is service.com) and "Data" (if the domain is data.com)

I tried with if and replace but didnt work

Thanks for the help
CT

 

[Anthony47]

Try, in C2:

=IF(B2="ABC",B2,MID(A2,FIND("@",A2)+1,FIND(".",A2,FIND("@",A2))-FIND("@",A2)-1))

Then copy down

Bye

 

[Habtest]

Hi. So you are essentially looking for the domain name between "@" and ".com", I would ignore the second column as it'd be easier to startover.
Try this in C2 and drag down.

=MID(A2,FIND("@",A2)+1,LEN(A2)-FIND("@",A2)-4)

The formula assumes it's always ".com" or ".???" at the end, let me know if otherwise.

 

[Lian]

工作簿1
	A	B	C
1	Sender_Email	COMPANY_NAME	Final
2	ABC@ABC.com	ABC	ABC
3	BCA@service.com	BCA	service
4	XYZ@data.com	BCA	data
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)

 

[chris1979]

Thank very much for your reply....it did work for most of the cells. I just discovered that deep down in the data set that there are different variables in the domain.

A	B	C
donotreply@xx.xx.cc.xx	IPM	IPM
donotreply@xx.xx.cc.xx	TTT	TTT
ns@service-abc.xxx.xxx.xx	XYZ	Service-AB
KC@sg.com.xx	XYZ	SG
donotreply@xx.xx.cc.xx	ST	ST

I have to make a change where

Condition

1. If the column B is equal to XYZ then go to column A...check the domain and give me the correct domain (like service-abc) in column C (highlighted) otherwise retain the same value as listed in the column B (like IPM,TTT etc)

Thanks a lot for your help

 

[Anthony47]

Excuse me, but are you really trying to get the company name from an email address?

 

[chris1979]

Yes Anthony....but the condition is if it is listed as XYZ then check the email address otherwise retain the same value as listed in column B

 

[Lian]

工作簿1.xlsx
	A	B	C
1	Sender_Email	COMPANY_NAME	Final
2	ABC@ABC.com	ABC	ABC
3	BCA@service.com	XYZ	SERVICE
4	XYZ@data.com	BCA	BCA
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(B2="XYZ",UPPER(MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)),B2)

 

[chris1979]

Thank you very much