Case-sensitive, but not sensitive enough

July 30, 2008 7:20 PM

Can Excel 2003 sort (or filter) primarily on case?

I have some data in Excel of mixed case-some all caps, some all lower case. I want to segregate the lower case items, so I can delete them. As far as I can tell, I can only set the sort function to make case the second level criterion-first it makes the list alphabetical, then sorts by case.

In other words, Excel gives me:

AB
ab
AC

when I want:

AB
AC
ab

Is there any way to sort (or filter, as a second best option) in this manner?

posted by Chrysostom to Computers & Internet (8 answers total)

I think what you're going to have to do is:

1. Add a new column
2. Create a formula in the new column that detects whether or not there is a capital letter in the specified place, and if so, mark an X.
3. Sort the column for the Xs and delete those rows.

The only thing is, I'm not sure what function can do that. I'll think about it.

The microsoft.com forums are really helpful, though.
posted by loiseau at 8:39 PM on July 30, 2008

Okay, here's a formula that will return "LOWERCASE" if a cell is lowercase, and "FALSE" if it's uppercase or mixed-case.

=IF(EXACT(A5,LOWER(A5)),"LOWERCASE")
posted by loiseau at 8:59 PM on July 30, 2008 [1 favorite]

Also, here's the link to the MS Office forums in case you're stumped in the future. I'm not the type to use these kinds of sites usually but they have paid experts there and they're pretty fast about replying to even the stupidest questions.
posted by loiseau at 9:03 PM on July 30, 2008

Sorry -- this is my last reply, really!! In that formula, replace the cellname A5 with the one you're targetting.
posted by loiseau at 9:07 PM on July 30, 2008

Are the values all the same case? That is, are the all "ab" or "AB" but not "aB"? If the strings are either lower or upper case, you could use the CODE function, which returns ASCII text values of the first character in the string.

To do this, type in "=CODE(cellref)" in a column next to one of your values, and then copy and paste that function into all the other cells in that column. Then sort using that column (the one populated with the ASCII values). The sort works because lower case values start at 97, while uppercase values will all be 90 or below, so the uppercase values will come first.
posted by jasper411 at 10:17 PM on July 30, 2008

Response by poster: No, they can be mixed case, too, but for my purposes, mixed is the same as upper.
posted by Chrysostom at 5:39 AM on July 31, 2008

Seconding the "add a dummy column" advice, but I would fill it with:

=IF(EXACT(B1,LOWER(B1)), "zzz" & B1, B1)

... and then sort by that column:

A B
1 AB AB
2 zzzab ab
3 AC AC
posted by IAmBroom at 1:00 PM on July 31, 2008

The spacing got edited out on me, but I hope you get the idea. The first row is column titles (A B), the next three are row numbers plus the first two cells.
posted by IAmBroom at 1:01 PM on July 31, 2008