Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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
    
If you're happy with the solution proposed - please accept the answer by ticking it, this will mark the question solved. For the details see: Accepting Answers: How does it work? –  Peter L. Feb 22 '13 at 7:39
    
Result not came properly, its 50% results only showing. –  user201373 Feb 22 '13 at 7:48

Here's an alternative approach

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.