Is it possible to filter on uppercase in Excel 2007? 2

[dianemarie]

Hello, I have a column of text. Some entries in the column are all uppercase. Is it possible to filter somehow on those cells that are uppercase? Or is there some other way to identify a cell with uppercase text? Thank you.

 

[SkipVought]

Hi,

"...Or is there some other way to identify a cell with uppercase text?"

Check out the CODE function.

The ASCII codes for lower case are in a different series from upper case characters.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[sxschech]

Here is a link for a function that determines upper lower case. Even though the article refers to Access, you can paste into an excel vba module (Tools Macro Macro) Then in a col you can enter as a function.

http://www.techonthenet.com/access/questions/uppercase.php

Here are the results from a test I did.

[tt]
Example:
A B C
1 xzy =islower(A1)
2 ABC =islower(A2)
3 Hij =islower(A3)


Results for Col B would look like
A B C
1 xzy TRUE
2 ABC FALSE
3 Hij TRUE

[/tt]

Then with this information, you can filter on Col B where Col B = FALSE to show only those rows that have only upper case.

 

[SkipVought]

And basically you can do a similar thing with the CODE funcition...
[tt]
=OR(CODE(A1)<CODE("A"),CODE(A1)>CODE("Z"))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[xlhelp]

Or, combining both suggestions, you could do:
=IF(CODE(A1)<97,1,2) in column B
and then sort column B as level 1 and column A as level 2 under Custom Sort.[Home tab, Sort and Filter dropdown]

Member- AAAA Association Against Acronym Abusers

 

[dianemarie]

Thank you everyone. SXS, your formula didn't work for me as islower doesn't appear to be a valid Excel function. I haven't had time to check out your link yet. Both Skip's and XL's formulas did work, and returned the same results on my dataset. However, for determining upper, the problem is it appears to look at only the first letter of the string? (see results below) I need it to look at the entire string and check for upper. If it's proper, that should return True (or 2), not False(or 1) using the formulas above. Is that possible? Thanks again for your help.

A.B. Lambdin FALSE 1
Abbey Blessings FALSE 1
Abbey Press Collectibles FALSE 1
Abbey Press Mail Order Buyers FALSE 1
Abbey Press Package Insert Program FALSE 1
babystyle - maternity, baby, kids TRUE 2
BRYLANEHOME LIFESTYLE FALSE 1

 

[sxschech]

In order for what I presented to work, you need to go to the web site using the link provided. There is some code there which you will then copy into the Macro Code window. Once the code has been pasted, then the formula will work. The example data shows that it can handle whole words in identifying the upper case only. Note, as long as one of the letters is in lower case, it will show TRUE. If you need further instructions on pasting the macro into the code for your excel file, let me know.

 

[dianemarie]

Thank you SXS. I'm investigating it now as this sounds like it will work exactly the way I need it to. I'll keep you posted.

 

[dianemarie]

Whoa! Super cool. It even addressed lists we have that start with numbers (1-800-Flowers) and I didn't even go into that as there aren't many and I figured it was outside the scope. It works perfectly and (long story) but this is going to be very helpful to us for necessary data analysis. I apologize again for not reading the directions completely the first time! Thanks again and God Bless.