Formula Required

  steviegee 10:37 14 Mar 2008
Locked

I need a formula that will tell me how many characters are over 30 in each cell. I have column B with about 10,000 rows and what I want to do is highlight that column and insert a formula that will say change the colour to red of any cell that has over 30 characters in it so I can reduce it. Thanks.

  johnnyrocker 10:41 14 Mar 2008

have uyou tried the 'if' function which should gie what you want?


johnny.

  steviegee 10:45 14 Mar 2008

Don't know how to use it!!

  johnnyrocker 10:48 14 Mar 2008

what about using find function on toolbar?



johnny.

  steviegee 10:51 14 Mar 2008

Well I cant use find can I? I cant type in 'find any cell with over 30 characters in it can I'!!!

  johnnyrocker 10:56 14 Mar 2008

sorry it is usually indicated as a pair of binoculars on toolbar ( i use open office which may be different to yours ) but thais how it is and i feel should be configurable to meet your needs.


johnny.

  johnnyrocker 11:10 14 Mar 2008

or try conditional formatting if it helps.


johnny

  xania 11:25 14 Mar 2008

click here counts words but for counting characters you want the LEN function, I quote:

LEN returns the number of characters in a text string.

LENB returns the number of bytes used to represent the characters in a text string.

Important LEN is intended for use with languages that use the single-byte character set (SBCS), whereas LENB is intended for use with languages that use the double-byte character set (DBCS). The default language setting on your computer affects the return value in the following way:

LEN always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
LENB counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, LENB counts each character as 1.

So you can use this to store the len of one field in another field say by putting the formula len A1 in B1; then you can set B1 to conditionally format (Format drop down menu) to a different colour if B1 is > 30

  UncleP 11:27 14 Mar 2008

Isn't there a function which returns the length of the text string in a given cell - sorry, it's twenty years since I last programmed spreadsheets, and we are talking about spreadsheets here, aren't we?

So the cell equation is conditional, as johnnyrocker suggested, of the form

IF length[cell_address]>30 THEN cell_colour=RED

This is NOT a working equation, but shows the form of what you need to generate with the spreadsheet language provided.

  Noldi 11:31 14 Mar 2008

Format toolbar > conditional format> cell value is-greater than 30. format colour as required.

Noldi

  Noldi 11:31 14 Mar 2008

Sorry just read it again ignor my solution.
Noldi

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Ryzen news - release date, UK price, features and specifications

The pulp art of 80s computer game magazine covers

Best value Mac: Which is the best £1249 Mac to buy

Comment faire des captures d’écran sous Windows 10 ?