Search for Upper Case

[Ken]

How can I search in a cell for any letter in a word that may be upper case?
For example I want to identify that a cell has a word such as ibuPROfen.

 

[Niek Otten]

If you just want to know if there is any uppercase character:

=EXACT(A9,LOWER(A9))

Returns FALSE if there is an uppercase character, TRUE otherwise

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| How can I search in a cell for any letter in a word that may be upper case?
| For example I want to identify that a cell has a word such as ibuPROfen.

 

[Ron Coderre]

with
A1: (text.....eg ibuPROfen)

This formula returns true if there are NO lower case letters:
B1: =EXACT(A1,LOWER(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Dave Peterson]

Maybe you could use a macro to select the cells that have any uppercase
characters:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim TempWks As Worksheet
Dim UpperCaseAddr As String

Application.ScreenUpdating = False

Set wks = Worksheets("Sheet1")
Set TempWks = Worksheets.Add

With wks
TempWks.Range(.UsedRange.Address).FormulaR1C1 _
= "=exact('" & .Name & "'!RC,lower('" & .Name & "'!RC))"
End With

With TempWks.Range(wks.UsedRange.Address)
.FormulaR1C1 _
= "=exact('" & wks.Name & "'!RC,lower('" & wks.Name & "'!RC))"
.Value = .Value
.Replace what:=True, _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False

UpperCaseAddr = ""
On Error Resume Next
UpperCaseAddr = .Cells.SpecialCells(xlCellTypeConstants).Address
On Error GoTo 0

End With

Application.DisplayAlerts = False
TempWks.Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

If UpperCaseAddr = "" Then
MsgBox "No uppercase characters!"
Else
Application.Goto wks.Range(UpperCaseAddr)
MsgBox "Tab through the selection"
End If

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ken]

This works perfectly, Thanks!

with
A1: (text.....eg ibuPROfen)

This formula returns true if there are NO lower case letters:
B1: =EXACT(A1,LOWER(A1))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)