Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-09
3
Medium Priority
?
502 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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

971 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