IF Formula for amount of Characters in a Cell

  • I need an IF formula or maybe even another logic formula value that will display one of two values (acceptable, unacceptable) if a cell had a set number of characters.


    Example Scenario:
    I have 4 cells that require the following:
    Cell B1 requires 9 charachters
    Cell B2 requires 16 characters
    Cell B3 requires 20 characters
    Cell B4 requires 10 characters


    I want Cells C1 - C4 to display Acceptable or Unacceptable based on the amount of characters in cells B1 - B4 (if there are 5 characters in B1, C1 should display unacceptable)


    Note: Can C1- C4 also contain a Conditional Format (when its acceptable the cell is green and if its unacceptable the cell is red, both with a white font for visibility).

  • Re: IF Formula for amount of Characters in a Cell


    The LEN function gives you the length of a cell in terms of characters. Conditional formatting is disregarded from.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: IF Formula for amount of Characters in a Cell


    Select the cell.
    On the file menu, select


    Data> Validation> Allow text length between 8 and 10 (for 9) etc.


    Will that work?

  • Re: IF Formula for amount of Characters in a Cell


    Shouldn't that be?


    =IF(LEN(B1)=9,"Acceptable","Unacceptable")
    =IF(LEN(B2)=16,"Acceptable","Unacceptable")
    =IF(LEN(B3)=20,"Acceptable","Unacceptable")
    =IF(LEN(B4)=10,"Acceptable","Unacceptable")


    Why allow unnacceptable char length however?

  • Re: IF Formula for amount of Characters in a Cell


    Thank you (Wigi, Max, and Bob)
    I used a combination of the Data Validation and LEN to accomplish my goal.


    thank you so much for your help. again.

  • Re: IF Formula for amount of Characters in a Cell


    Max, To answer your question "Why allow unnacceptable char length however?" the some values hard values that I don't want the user to enter anything more or less than the required and I used the Data Validation, in one specific area I want to allow the user to enter any value but be shown the values is not the correct length.


    Thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!