Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need a formula in excel with if condition.

if the mail id abcd@xyz.com then the result should come as XYZ, if the mail id is abcd@.in.xyz.com then the result should come as XYZ.

result should be XYZ if any of the above mail id is there.

for the first one i got the formula as if mail id is in C2 then the formula is as

UPPER(SUBSTITUTE(LEFT(MID(C2,FIND("@",C2)+1,LEN(C2)),FIND(".",MID(C2,FIND("@",C2)+2,LEN(C2)))),"."," "))
share|improve this question
    
does it have to be in one formula or can you use helper columns? – Peter Albert Feb 22 '13 at 6:49
    
need in a one formula using if – user201373 Feb 22 '13 at 6:55
up vote 4 down vote accepted

Assuming your email is in A1 - use this monster:

=UPPER(MID(A1,IFERROR(FIND("#",SUBSTITUTE(A1,".","#",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1)),FIND("@",A1))+1,FIND("#",SUBSTITUTE(A1,".","#",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-IFERROR(FIND("#",SUBSTITUTE(A1,".","#",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1)),FIND("@",A1))-1))

Sample file: https://www.dropbox.com/s/23p3a7hf9pbodiw/OrgEmail.xlsx

share|improve this answer
    
I'm sorry, it doesn't have IF - I used IFERROR instead..) – Peter L. Feb 22 '13 at 7:15
    
if the mail is abcd@xyz.com after applying this formula i am getting the result as ABCD@XYZ and for abcd@in.xyz.com its the result is coming correct as XYZ. – user201373 Feb 22 '13 at 7:21
    
@user201373 did you check the sample? it has this e-mail working nice. – Peter L. Feb 22 '13 at 7:22
    
Result not came properly, its 50% results only showing. – user201373 Feb 22 '13 at 7:48
    
@user201373 could you please provide samples which do not work? 2-3 will be enough. – Peter L. Feb 22 '13 at 7:49

Here's an alternative approach

=UPPER(TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"@","."),".",REPT(" ",100)),200),100)))

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .