Identify first lower case letter in string

melewie

Board Regular
Joined
Nov 21, 2008
Messages
188
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am having problems (again!!!). I am trying to idenify the first lower case letter in a string. Below is the cloest I have got (It doesnt work at all but doesnt debug)

Code:
[SIZE=2]Option Explicit
Sub Test1()
Dim StrLen As Integer
Dim ChrNum As Integer
Dim i As Integer
Dim j As Integer
Dim Exitfor As Boolean
Dim str As String
 
[COLOR=black][FONT=Verdana]StrLen = Len(Selection)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]str = Selection.Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For i = 1 To StrLen<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exitfor = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For j = 97 To 122<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If InStr(i, str, Chr(j), vbBinaryCompare) = 1 Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exitfor = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit For<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox Chr(j)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next j<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Exitfor = True Then Exit For<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]MsgBox i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[/SIZE]

Any help would be great, thanks in advance
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Maybe:

Code:
Sub Test1()
Dim i As Integer
Dim str As String
 
str = Selection.Value
For i = 1 To Len(str)
If Mid(str, i, 1) <> UCase(Mid(str, i, 1)) Then
MsgBox "First lower case is: " & Mid(str, i, 1)
Exit Sub
End If
Next i
MsgBox "No lower case"
End Sub

Dom
 
Upvote 0
HTML:
Option Explicit
Sub Test1()
Dim StrLen As Integer
Dim Exitfor As Boolean
Dim i As Integer
Dim j As Integer
Dim str As String

str = Selection.Value
StrLen = Len(str)
For i = 1 To StrLen
  j = Asc(Mid(str, i, 1))
  If j >= 97 And j <= 122 Then
    MsgBox i & ", " & Mid(str, i, 1)
    Exitfor = True
    Exit For
  End If
Next i
If Not Exitfor Then MsgBox "No lower case letter found"
End Sub
 
Upvote 0
or for fun:
Code:
Sub test2()
Dim x
x = Evaluate("=MATCH(1,(CODE(MID(""" & Selection.Value & """,ROW(1:1000),1))>=97)*(CODE(MID(""" & Selection.Value & """,ROW(1:1000),1))<=122),0)")
If Not IsError(x) Then MsgBox x & ", " & Mid(Selection, x, 1) Else MsgBox "No lower case found"
End Sub
If you wanted this as just worksheet formulae instead:
if the string to be tested was in A1, then in any other cells, both ARRAY-ENTERED,
=MATCH(1,(CODE(MID(A1,ROW($1:$1000),1))>=97)*(CODE(MID(A1,ROW($1:$1000),1))<=122),0)
and
=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$1000),1))>=97)*(CODE(MID(A1,ROW($1:$1000),1))<=122),0),1)
will give the position and the letter in the string respectively.
 
Last edited:
Upvote 0
For what it's worth, here's a similar way...

=MATCH(1,IF(ABS(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-109.5)<=12.5,1),0)

...confirmed with COTROL+SHIFT+ENTER.
 
Upvote 0
Hi All,

Cheers for the help everyone. I will try this again today.

thanks again

melewie
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top