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!



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"," ")




  • 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!