Solved

How do I check for any lower case letters in a col in excel ?

Posted on 2013-01-09
3
482 Views
Last Modified: 2013-01-09
I have much data to check

I need to know if  col (A) is in all caps.
I need to know if co (B) is in all lower case.

If I make a mistake my boss says he will apply "capital punishment"

thx


Excel 07
0
Comment
Question by:TIMFOX123
3 Comments
 
LVL 70

Expert Comment

by:KCTS
ID: 38761124
I came across this code on Mr Excel, which would do what you want with any selected range

Public Sub Format_Upcase()
 Application.ScreenUpdating = False 'speed up with large selections
 Application.Calculation = xlManual

 For Each xCell In Selection
 If UCase(xCell.Text) <> xCell.Text Then xCell.Value = UCase(xCell.Text)
 Next

 Application.Calculation = xlAutomatic
 Application.ScreenUpdating = True
 End Sub

 Just highlight the cells that must be in upper case and and run the macro
If you want to put things into lower case then use this macro instead

Public Sub Format_Locase()
 Application.ScreenUpdating = False 'speed up with large selections
 Application.Calculation = xlManual

 For Each xCell In Selection
 If LCase(xCell.Text) <> xCell.Text Then xCell.Value = LCase(xCell.Text)
 Next

 Application.Calculation = xlAutomatic
 Application.ScreenUpdating = True
 End Sub
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38761142
The following formula returns TRUE if A2 contains all upper case:

=NOT(ISERROR(FIND(UPPER(A2),A2)))

For all lower case:

=NOT(ISERROR(FIND(LOWER(A2),A2)))
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38761221
Formulawise you could use EXACT function, i.e. UPPER CASE in A2

=EXACT(A2,UPPER(A2))

and for B2 in lower case

=EXACT(B2,LOWER(B2))

regards, barry
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question