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?


You can use OR like this to search for multiple letters

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


You can try this way:-

  • 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
  • @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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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