Finding cells with a specific number of characters

K

Kamran

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.
 
C

Chip Pearson

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
 
R

Ron Coderre

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
 
K

Kamran

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.
 
K

Kamran

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.
 
P

Peo Sjoblom

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
 
Top