2 Replies Latest reply: Apr 17, 2013 11:33 AM by composerguy78
composerguy78 Level 1 (25 points)

How do I get a column B to display X if Column A contains X?

 

I know this must be really simple, but I can't find it in the help!

Thx

Felix


Mac Pro, OS X Mountain Lion (10.8.2), 12 GB RAM, Motu 828 Audio interface FW 400
  • Barry Level 7 (29,899 points)

    Ambiguous question. Will the cell in A contain only a single letter or a string of one or more characters? Should the formula detect only lower case x, upper case X, or be case insensitive?

     

    Here's a general solution:

    Picture 3.png

    Formula in all cells in column B: =IF(OR(LEN(SUBSTITUTE(A,"x",""))<LEN(A),LEN(SUBSTITUTE(A,"X",""))<LEN(A)),"X"," ")

     

    Regards,

    Barry

  • composerguy78 Level 1 (25 points)

    HI Barry,

    My apologies - ambiguous indeed!

     

    That totally solved it for me! I manged to reverse engineer the formula a bit and it's doing just what I want.

     

    Much appreciated!
    Felix