Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert lower to upper case automatically without using UPPER | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |