Searching for Capital Letters
VERY good...thank you,
FLKulchar
"Tom Ogilvy" wrote in message
...
think Roger was closer on his first attempt:
=IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 ))
entered with CTrl+Shift+Enter. Change the 5 at the end of the above
formula
to indicate which Capital letter position you want. Change it to a 1, you
get the first.
It will display an error value if you don't have any or have less than you
ask for.
--
Regards,
Tom Ogilvy
"FLKulchar" wrote:
Unfortunately, my VBA knowledge is totally nonexistent...can you perform
my
query using the EXCEL functions?
Thanks,
FLKulchar
"Roger Govier" wrote in message
...
Hi
Hmmmm.....
Easiest with the VBA solution
Sub test5()
Dim i As Long, j As Long
Dim s As String, c As String
s = ActiveCell.Value
j = 0
For i = 1 To Len(s)
c = Mid(s, i, 1)
If Asc(c) 64 And Asc(c) < 91 Then
j = j + 1
If j = 5 Then
MsgBox "Fifth Uppercase Alpha Position is " & i
Exit Sub
End If
End If
Next
MsgBox "No Fifth Uppercase Alpha in " & s
End Sub
I will need to give the formula solution a little more thought!!!!
--
Regards
Roger Govier
"FLKulchar" wrote in message
...
GREAT...
Now, suppose I want to test for the 5th capital letter??
What changes in your fabulous formula?
Thank you,
FLKulchar
"Roger Govier" wrote in message
...
Hi
Just realised you asked in .programming so you probably wanted a code
solution not a formula.
Something like the following should get you started.
Sub test()
Dim i As Long
Dim s As String, c As String
s = ActiveCell.Value
For i = 1 To Len(s)
c = Mid(s, i, 1)
If Asc(c) 64 And Asc(c) < 91 Then
MsgBox "First Uppercase Alpha Position is " & i
Exit Sub
End If
Next
End Sub
--
Regards
Roger Govier
"Roger Govier" wrote in message
...
Hi
Rather unwieldy, but seems to produce the desired result, the array
entered
{=IF(A1="","",
SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))*
(--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))*
ROW(INDIRECT("1:"&LEN(A1)))))}
Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter.
Excel will insert the curly braces { } when you use CSE, do not
type them yourself.
--
Regards
Roger Govier
"F. Lawrence Kulchar"
wrote in message
...
How do I find the position of the FIRST...(or fifth) capital letter
within a
text string?
EG:
A
1 abndHnmJ
2 123wTTghy6TH
3 hhhHhhh
ANSWER: A1 would be 5
A2 would be 5
A3 would be 4
HOW TO TEST FOR THIS??
Thank you,
FLKulchar
|