Hello:

All I know is that the formulae work...I am F. L. Kulchar...I started the
queries.

I want to thank all of you for your unbelievably intelligent input...I wish
I could be "near" you so I could learn more.

FLKulchar
"NickHK" wrote in message
...
Roger,
I'm the other way ; avoid formulae in favour of functions.

NickHK

"Roger Govier"
bl...
Hi Nick

I agree - but I have never been good at writing functions, perhaps I
should practice some more!!

To the OP's second post, I did give some VBA code first, but he asked for
a formula solution.

Your UDF works absolutely fine and I will use this as a guide to trying
to write a few more for me to play with. Thank you.

--
Regards

Roger Govier


"NickHK" wrote in message
...
Roger,
Your formula seems to give the correct answer, but for me, it's for
situations like this that god gave us UDFs.

To the OP ;
From the worksheet, press Alt+F11to open the VBE.
Right-click on the the entries your see for your current workbook,
something like "Sheet1" I suppose.
Select New Module.
Paste the code below in this new module.

Then, from a worksheet cell, enter "=GetCapitalLetterPosition(A2,1,5)"

where A2 is the address of the word you want to examine.

NickHK

Public Function GetCapitalLetterPosition(argStr As Variant, Optional
StartAtPosition As Long = 1, Optional Occurence As Long = 1) As Long
Dim LowerCase As String
Dim i As Long
Dim OccurenceCount As Long

LowerCase = LCase(argStr)

For i = StartAtPosition To Len(LowerCase)
If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then
OccurenceCount = OccurenceCount + 1
If OccurenceCount = Occurence Then
GetCapitalLetterPosition = i
Exit Function
End If
End If
Next

GetCapitalLetterPosition = 0
End Function

"Roger Govier"
bl...
Hi

are you sure you're ready fro this<vbg

{=IF(A2="","",
LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))*
(--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))*
ROW(INDIRECT("1:"&LEN(A2))),
SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))*
(--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))}

Again array entered with CSE.
the part
SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))*
(--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64)))
entered as an array formula, counts the number of CAPITAL letters in
the string
I made the last part +1-5 as the 5 is the 5th capital letter and is
therefore easier to see than using a net 4.

To prevent errors with strings less than 5 capital letters, you should
really use this again at the beginning of the formula to provide an
additional IF test, to give a null value in this scenario.


--
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