Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 have list of person names in column A.

I want to display "1" in column B for names which end with either "e" or "i" or "n".

If there would be only one match to test against, I would write something like:

=IF( MID(A1,FIND(" ",B1)-1,1) = "e", "1", "0")

In PHP I would solve that like this:

echo in_array( $names[$row_number], array('e', 'i', 'n') ) ? '1' : '0';

What formula should I use in column B in Excel?

share|improve this question
up vote 1 down vote accepted

You can use OR like this to search for multiple letters

=IF(OR(MID(A1,FIND(" ",A1)-1,1)={"e","i","n"}),1,0)

share|improve this answer

You can try this way:-

share|improve this answer
This is so far the shortest solution. I tested it and it works. However, I don't understand how OR() works here. Looks as if there is only one expression in it's argument list... – Henno Mar 18 '12 at 18:59
@Henno: Here OR works as like this =OR(A1="e", A1="i", A1="n") which returns TRUE or FALSE – Siva Charan Mar 18 '12 at 19:13
@Henno, your existing formula with MID implies that you are looking for "e" as the last letter of the first name (the letter before the space) rather than the last letter in the cell, hence my answer using MID too but utilising OR.... – barry houdini Mar 18 '12 at 19:36
@Siva thank you for clarifying that. – Henno Mar 18 '12 at 19:42
@barry You are right! I missed that until now. The reason it worked for me was that by chance I actually had only the first name in the A column when I tested this. Silly me. :-) – Henno Mar 18 '12 at 19:44

Your Answer


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.