filtering to separate upper & lower case
 

I have a spreadsheet with one column data containing text, only differing by
being in either upper or lower case. I need to filter uppercase from lower
case but can't find how. Advance filter does not seem to cover case sensitive
any suggestions, perhaps another way of doing this ?

filtering to separate upper & lower case
 

Hi,

Do you want to filter to another location all, the text which have their
first alphabet as lowercase? Please give examples.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"GaryC" wrote in message
...
I have a spreadsheet with one column data containing text, only differing
by
being in either upper or lower case. I need to filter uppercase from lower
case but can't find how. Advance filter does not seem to cover case
sensitive
any suggestions, perhaps another way of doing this ?

filtering to separate upper & lower case
 

Maybe use a helper col ..

Assuming source text in A2 down
In B2, copied down:
=IF(A2="","",IF(AND(CODE(LEFT(A2))=65,CODE(LEFT(A 2))<=90),"Upper",IF(AND(CODE(LEFT(A2))=97,CODE(LE FT(A2))<=122),"Lower","")))
will flag upper/lower as desired, based on the leftmost character. Then just
apply/use autofilter on col B

Success? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"GaryC" wrote:
I have a spreadsheet with one column data containing text, only differing by
being in either upper or lower case. I need to filter uppercase from lower
case but can't find how. Advance filter does not seem to cover case sensitive
any suggestions, perhaps another way of doing this ?

filtering to separate upper & lower case
 

On Mon, 3 Aug 2009 23:18:01 -0700, GaryC
wrote:

I have a spreadsheet with one column data containing text, only differing by
being in either upper or lower case. I need to filter uppercase from lower
case but can't find how. Advance filter does not seem to cover case sensitive
any suggestions, perhaps another way of doing this ?

You certainly can use the Advanced Filter. You need to use formulas for
criteria.

Here is an example.

Table set starting at

A4: Data

Data Values
NOW IS 2
now is 3
FOR ALL 4
for all 5

Criteria (to extract lower case text lines)

A1: <empty cell
A2: =EXACT(A5,LOWER(A5))

Advanced Filter
List Range: $A$4:$B$8
Criteria RAnge: $A$1:$A$2

The result, is:

Data Values
now is 3
for all 5

To extract the upper case entries,
A2: =upper(a5,exact(a5))
--ron

filtering to separate upper & lower case
 

=IF(A2="","",IF(AND(CODE(LEFT(A2))=65,CODE(LEFT(A 2))<=90),"Upper",IF(AND(CODE(LEFT(A2))=97,CODE(LE FT(A2))<=122),"Lower","")))

CODE() it takes the first character, so you no need LEFT()


"Max" wrote:

Maybe use a helper col ..

Assuming source text in A2 down
In B2, copied down:
=IF(A2="","",IF(AND(CODE(LEFT(A2))=65,CODE(LEFT(A 2))<=90),"Upper",IF(AND(CODE(LEFT(A2))=97,CODE(LE FT(A2))<=122),"Lower","")))
will flag upper/lower as desired, based on the leftmost character. Then just
apply/use autofilter on col B

Success? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"GaryC" wrote:
I have a spreadsheet with one column data containing text, only differing by
being in either upper or lower case. I need to filter uppercase from lower
case but can't find how. Advance filter does not seem to cover case sensitive
any suggestions, perhaps another way of doing this ?

filtering to separate upper & lower case
 

CODE() it takes the first character, so you no need LEFT()
You're right. Thanks for the correction.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---