Total Text Length of Adjacent Cells

[sammy]

Hi All,

I have two adjacent cells (E13 and F13) and i want to restrict the length of
the text so that their ~combined~ length is less than 9 characters. I tried
to use data validation with "custom" and the formula like:
Len(E13)+Len(F13)<=9
but as most of you know, this didn't work.
Any help is appreciated.
TIA

 

[Per Jessen]

Hi

You are very close...

With both cells selected use this validation formula:

=LEN($E$13)+LEN($F$13)<=9

Notice the formula shall always start with an equal sign, and I used
absolute references.

Hopes this helps.
....
Per

 

[EricG]

That didn't quite work for me. After applying the validation, I entered
something like "aaaaaaaaaaaaaaaaaaa" in either cell, with the other blank,
and it was allowed. The validation only seems to work when both cells have
something in them.

Eric

 

[Per Jessen]

I did not test that before, but it works if you uncheck "Ignore blanks".

Per

 

[EricG]

You are right. However, it still seems like a bug. If "Ignore Blanks" is
checked, it should still see that one of the cells has too many characters,
and put up the warning message.

 

[Per Jessen]

I don't think it is a bug.

When "Ignore Blanks" is checked validation ignore the rule if one of the
cells in the formula is blank. However, intuitively it is not what one would
expect to happen.

 

[sammy]

Thank you Per Jessen.

Hi

You are very close...

With both cells selected use this validation formula:

=LEN($E$13)+LEN($F$13)<=9

Notice the formula shall always start with an equal sign, and I used
absolute references.

Hopes this helps.
....
Per