Limiting entries in calc.

[Robert Jackson-6]

I am looking for a method to limit text entries in a calc spreadsheet to
21 characters.
Failing that I would like a macro or basic function that would scan all
the cells and highlight those with too many characters.

Thanks for any suggestions
 - Bob


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Johnny Rosenberg]

2009/11/6 Robert Jackson <robertjackson@...>:
> I am looking for a method to limit text entries in a calc spreadsheet to 21
> characters.

Have no good answer to that one, sorry. Well, except writing a macro
that scan all cells and cut all strings from the 22nd character, of
course…

> Failing that I would like a macro or basic function that would scan all the
> cells and highlight those with too many characters.

That is an easy one, and no macro is required:
F11 → Select Default → Right Click → Modify… → Do the change, maybe
change background colour or something like that → Change the name of
the style to ”TooLong”, for example → OK → Select A1 → Ctrl+a → Format
→ Conditional formatting… → Change from ”Cell value is” to ”Formula
is” → In the text input field to the right: LEN(A1)>21 → Cell style:
TooLong → OK → Select a cell or something… → Input some text in some
cells, some of them more than 21 characters, some of them not →
Mission completed…

Johnny Rosenberg

>
> Thanks for any suggestions
> - Bob
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Andreas Saeger]

Robert Jackson wrote:
> I am looking for a method to limit text entries in a calc spreadsheet to
> 21 characters.
> Failing that I would like a macro or basic function that would scan all
> the cells and highlight those with too many characters.
>
> Thanks for any suggestions
> - Bob

Data>Validity...
Allow: "Text Lenght"
Error Alert>Action: "Stop"

Unlike databases spreadsheets have no concept of data types and field
lengths. By intention, spreadsheets allow any number and any text in any
cell. Validation is not even child proof. A simple copy&paste can
overide your rules.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Robert Jackson-6]

Andreas Saeger wrote:

> Robert Jackson wrote:
>> I am looking for a method to limit text entries in a calc spreadsheet
>> to 21 characters.
>> Failing that I would like a macro or basic function that would scan
>> all the cells and highlight those with too many characters.
>>
>> Thanks for any suggestions
>> - Bob
>
> Data>Validity...
> Allow: "Text Lenght"
> Error Alert>Action: "Stop"
>
> Unlike databases spreadsheets have no concept of data types and field
> lengths. By intention, spreadsheets allow any number and any text in
> any cell. Validation is not even child proof. A simple copy&paste can
> overide your rules.
>

Johnny Rosenberg wrote:

> Have no good answer to that one, sorry. Well, except writing a macro
> that scan all cells and cut all strings from the 22nd character, of
> course…
>
>  
>> Failing that I would like a macro or basic function that would scan all the
>> cells and highlight those with too many characters.
>>    
>
> That is an easy one, and no macro is required:
> F11 → Select Default → Right Click → Modify… → Do the change, maybe
> change background colour or something like that → Change the name of
> the style to ”TooLong”, for example → OK → Select A1 → Ctrl+a → Format
> → Conditional formatting… → Change from ”Cell value is” to ”Formula
> is” → In the text input field to the right: LEN(A1)>21 → Cell style:
> TooLong → OK → Select a cell or something… → Input some text in some
> cells, some of them more than 21 characters, some of them not →
> Mission completed…
>
> Johnny Rosenberg
>
>
>  
>> Thanks for any suggestions
>> - Bob
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>>    

Thanks to all. This does it.

-Bob