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 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
add comment

2 Answers

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
add comment

You can try this way:-

=IF(OR(RIGHT(A1,1)={"e","i","n"}),1,0)
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
add comment

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.