 # 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.

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

=IF(LEN(B1)=5,"Acceptable","Unacceptable")

HTH

Bob

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