I'm trying to look for the email service provider in column A, which contains a series of email addresses.
In column K, I want to print out if the email in column A is a 'gmail', 'yahoo', or 'other' email account.
I can use the below formula to check if "gmail" exists:
=IF(COUNTIF(A2,"*gmail*"),"gmail")
As of right now, it will spit out FALSE if gmail does not exist in the A column, but it does spit out if the email in column A is a gmail.
How do I add to the formula to check for 'yahoo', and everything else as 'other'?
If you just need to test these two this is the formula. It checks for gmail, and as you experienced, if it doesn't it goes further to see if it finds yahoo the same way, and if it doesn't find either it is labelled as "other"
=IF(COUNTIF(A2,"*gmail*"),"gmail", if(COUNTIF(A2,"*yahoo*"),"yahoo", "other" ))
You could also use a combination of FIND, LEN, LEFT, & RIGHT to extract the website of the email. You can split up the steps in a LET formula to clean it up, but this is just to get it to work on it's own.
If your data was in A1 this is a formula that will pull the website from an email:
=LEFT(RIGHT(A1,LEN(A1)-FIND("@",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("@",A1)))-4)