Total Text Length of Adjacent Cells

S

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
 
P

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
 
E

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
 
P

Per Jessen

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

Per
 
E

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

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

sammy

Thank you Per Jessen.

Per Jessen said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top