Finding cells with a specific number of characters
 

I'm trying to find all the cells in a column that have more than 250
characters. I've tried using DataFilterAdvanced, but haven't been able to
figure it out. I appreciate any help.

Finding cells with a specific number of characters
 

In a blank column next to your data, enter the following formula
and copy down as far as you need to go.

=LEN(A1)250

This will return TRUE and FALSE values. Now, auto-filter on TRUE.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Kamran" wrote in message
...
I'm trying to find all the cells in a column that have more
than 250
characters. I've tried using DataFilterAdvanced, but haven't
been able to
figure it out. I appreciate any help.

Finding cells with a specific number of characters
 

If you want to use Advanced Filter...here's how:

Assuming your list begins in A6, with the column heading in A5
Example:
A5: TextField
A6: Some text of variable length that may or may not exceed 250 chars
etc

Then:
A1: LenTest
A2: =LEN(A6)250

Next: Select your text values A5 through the bottom of the list
<Data<Filter<Advanced Filter
Uncheck: Copy to another location
Uncheck: Unique records only
List Range: (already selected)
Criterial Range: $A$1:$A$2
Click the [OK] button

You should now see only cells that are longer than 250 chars.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kamran" wrote:

I'm trying to find all the cells in a column that have more than 250
characters. I've tried using DataFilterAdvanced, but haven't been able to
figure it out. I appreciate any help.

Finding cells with a specific number of characters
 

I appreciate the replies, but I've been unable to make it work. My data is
in column F, heading in F1, data starting in F2. Using the suggested formula
[in my case, =LEN(F2)250], it hides all rows, I think because cell A6 has
less than 250 char and therefore the criterion is not met. Shouldn't it be
checking the F column independently for every row, e.g., =LEN(F2)250,
=LEN(F3)250, =LEN(F4)250 ?
The only way I've been able to make it work is to copy the F column heading
into a blank column, and the enter 250 question marks into the cell below and
use that for the Advanced Filter.

Finding cells with a specific number of characters
 

Thanks, Chip. After several unsuccessful trials, I finally realized that the
column with the LEN statements had to be "next" to the one with the data I'm
analyzing. It worked.

Finding cells with a specific number of characters
 

It works so you must have applied it incorrectly, you need to have a header,
if you want to filter in place it's easier if you insert a couple of empty
rows at the top, also make sure that the criteria includes 2 cells, one
empty above and the one with the formula below so if the formula is in H2
use H1:H2 for the criteria. Or you can use Chips method copying the formula
down in a help column and filter autofilter on that help column



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Kamran" wrote in message
...
I appreciate the replies, but I've been unable to make it work. My data is
in column F, heading in F1, data starting in F2. Using the suggested
formula
[in my case, =LEN(F2)250], it hides all rows, I think because cell A6 has
less than 250 char and therefore the criterion is not met. Shouldn't it
be
checking the F column independently for every row, e.g., =LEN(F2)250,
=LEN(F3)250, =LEN(F4)250 ?
The only way I've been able to make it work is to copy the F column
heading
into a blank column, and the enter 250 question marks into the cell below
and
use that for the Advanced Filter.

Finding cells with a specific number of characters
 

It doesn't have to be next to the column with the data but it needs to have
a filter applied to it and it is easier to use if it is next to it

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Kamran" wrote in message
...
Thanks, Chip. After several unsuccessful trials, I finally realized that
the
column with the LEN statements had to be "next" to the one with the data
I'm
analyzing. It worked.