I have thousands of rows in my spreadsheet and I need to identify the cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

tks,
steve

sjs wrote...
I have thousands of rows in my spreadsheet and I need to identify the cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

Strictly more than 30 characters? Edit Find, Find what entry should
be

???????????????????????????????

that's 31 question marks. Alternatively, use conditional formatting
with the formula condition for cell D5 being =LEN(D5)30 and give it a
distinctive format (IMO, different background colors work best).

Assuming that Column A contains your data, enter the following formula
in B1, and copy down:

=(LEN(A1)30)+0

The formula will return 0 for cells containing less than 30 characters,
and 1 for those containing more than 30 characters.

Hope this helps!

In article ,
sjs wrote:

I have thousands of rows in my spreadsheet and I need to identify the cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

tks,
steve

Hi Steve

You can use an IF function on another sheet eg =IF(LEN(Sheet1!A1)30,1,"")
and then use that data somehow. You don't say what you want to do with your
cells containing more than 30 characters. If you want to convert them all
to 30 characters max, then you can enter a formula on Sheet2, eg
=LEFT(Sheet1!A1,30), copy it down and across and then convert it all to
values....

HTH
Best rgds
Chris Lav



"sjs" wrote in message
...
I have thousands of rows in my spreadsheet and I need to identify the
cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

tks,
steve

Excellent, thanks!!

"Harlan Grove" wrote:

sjs wrote...
I have thousands of rows in my spreadsheet and I need to identify the cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

Strictly more than 30 characters? Edit Find, Find what entry should
be

???????????????????????????????

that's 31 question marks. Alternatively, use conditional formatting
with the formula condition for cell D5 being =LEN(D5)30 and give it a
distinctive format (IMO, different background colors work best).

Perfecto!! Thanks!

"Domenic" wrote:

Assuming that Column A contains your data, enter the following formula
in B1, and copy down:

=(LEN(A1)30)+0

The formula will return 0 for cells containing less than 30 characters,
and 1 for those containing more than 30 characters.

Hope this helps!

In article ,
sjs wrote:

I have thousands of rows in my spreadsheet and I need to identify the cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

tks,
steve

Hi Chris,

Thanks for the help...I simply need to identify fields that exceed 30
characters then I need to shorten them appropriately, the fields are product
descriptions that I'll import into a business system. The "LEN" command to
id the cells works like a champ!

Have a great weekend!

steve

"Chris Lavender" wrote:

Hi Steve

You can use an IF function on another sheet eg =IF(LEN(Sheet1!A1)30,1,"")
and then use that data somehow. You don't say what you want to do with your
cells containing more than 30 characters. If you want to convert them all
to 30 characters max, then you can enter a formula on Sheet2, eg
=LEFT(Sheet1!A1,30), copy it down and across and then convert it all to
values....

HTH
Best rgds
Chris Lav



"sjs" wrote in message
...
I have thousands of rows in my spreadsheet and I need to identify the
cells
that contain more than 30 characters. I know how to do a Validation on a
cell but that only flags me when I enter data into the formatted cell, not
when data already exists. Any ideas?

tks,
steve