filtering to separate upper & lower case

[GaryC]

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 ?

 

[Ashish Mathur]

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

 

[Max]

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(A2))<=90),"Upper",IF(AND(CODE(LEFT(A2))>=97,CODE(LEFT(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

 

[Ron Rosenfeld]

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

 

[Teethless mama]

=IF(A2="","",IF(AND(CODE(LEFT(A2))>=65,CODE(LEFT(A2))<=90),"Upper",IF(AND(CODE(LEFT(A2))>=97,CODE(LEFT(A2))<=122),"Lower","")))

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

 

[Max]

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