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 then the result should come as XYZ, if the mail id is 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:


Sample file:

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 after applying this formula i am getting the result as ABCD@XYZ and for 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 .