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 Data>Filter>Advanced, but haven't been able to
figure it out. I appreciate any help.

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" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to find all the cells in a column that have more
> than 250
> characters. I've tried using Data>Filter>Advanced, but haven't
> been able to
> figure it out. I appreciate any help.

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 Data>Filter>Advanced, but haven't been able to
> figure it out. I appreciate any help.

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.

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.

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" <[email protected]> wrote in message
news:[email protected]...
>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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.