| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total Text Length of Adjacent Cells
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
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 "sammy" skrev i meddelelsen ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
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" wrote: 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 "sammy" skrev i meddelelsen ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
I did not test that before, but it works if you uncheck "Ignore blanks".
Per "EricG" skrev i meddelelsen ... 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" wrote: 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 "sammy" skrev i meddelelsen ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
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" wrote: I did not test that before, but it works if you uncheck "Ignore blanks". Per |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
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. "EricG" skrev i meddelelsen ... 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" wrote: I did not test that before, but it works if you uncheck "Ignore blanks". Per |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Total Text Length of Adjacent Cells
Thank you Per Jessen.
"Per Jessen" wrote: 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 "sammy" skrev i meddelelsen ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract text on right of various length from cells | Excel Discussion (Misc queries) | |||
| Total based on text dependancy + number value in adjacent cell | Excel Discussion (Misc queries) | |||
| How do I combine text from adjacent cells? | Excel Worksheet Functions | |||
| length of text string goes beyond cells are not visible | Excel Discussion (Misc queries) | |||
| add 3 non-adjacent % cells visually = 99.9, total shows 100.0 | Excel Worksheet Functions | |||
Hybrid Mode
