truncate

[cathal]

how can I limit the contents of a cell to 100 words? In other words, i
a cell contains 105 can I have a cut-off at 100?

thanks - catha

 

[Govind]

Hi,

If your data is in A1, you can enter add a helper column B,and enter
thsi formula in B1.

=LEFT(A1,MIN(100,A1))

This will trucate the values in column A to 100.

Regards

Govind.

 

[JE McGimpsey]

One way, sort of...

You can limit the number of spaces. Data/Validation:

Allow: Custom
Formula: =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) < 100

You could ignore double spaces:

Allow: Custom
Formula: =LEN(SUBSTITUTE(A1," "," "))-LEN(SUBSTITUTE(SUBSTITUTE(A1,
" "," ")," ","")) < 100

but that wouldn't catch triple-spaces.

Neither would catch words separated by hyphens or n-dashes, ellipses,
etc.