I have been trying to pull the company name and country from an email address. A member has been amazingly helpful so far but I think I may have killed my previous thread.

Basically the main mistakes in my "Company Name" field were assigned to a handful of pre suffixes. For instance @za.pwc.com - @tz.ibm.com. There appear to be just a a few of these pre suffixes which refer to countries. So with those examples I want the country to be assigned by the first two letters "za" and "tz" and the company to be the bit after the first.

At the moment my formula only considers the last two letters, which need to remain.

So to do this, can we do a new IF within the formula, so IF equals "za", "ae", "za", "tz", "eu" or "ts". Then it applies a different rule. Perhaps I may need to edit this list but I do not envisage it getting too long. Ideally it would look these differences up in a LOOKUP. So sheet2 could have.

za - South Africa
tz - Tanzania

etc....


For the company name as it is;

=IF(ISNUMBER(MATCH(PROPER(LEFT(REPLACE(A2,1,FIND("@",A2),""),FIND(".",REPLACE(A2,1,FIND("@",A2),""))-1)),Sheet2!$L$2:$L$15,0)),"",PROPER(LEFT(REPLACE(A2,1,FIND("@",A2),""),FIND(".",REPLACE(A2,1,FIND("@",A2),""))-1)))

Would I be really over complicating it by adding a lookup for certain company names. i.e. if pwc - return Price Waterhouse Coopers, uf - return University of the Free State. etc. . . .