Using Bernd's formula (an approach I considered but abandoned because of
numbers in the string, but he did it the right way).

You could do something like this:

=SMALL(IF(CODE(MID(A1&REPT("
",30),ROW(INDIRECT("1:30")),1))<CODE(MID(LOWER(A1 )&REPT("
",30),ROW(INDIRECT("1:30")),1)),ROW(1:30)),B1)

then it becomes

=SMALL(IF(CODE(MID(A1&REPT(" ",30),seq,1))<CODE(MID(LOWER(A1)&REPT("
",30),seq,1)),ROW(1:30)),B1)

where seq is defined as ROW(INDIRECT("1:30"))

There may be a cleverer way, but nothing jumped to mind.

--
Regards,
Tom Ogilvy




"Roger Govier" wrote:

Hi Tom

Thank you for that.
I had attempted using SMALL( ,5) first but came up with the position of
the first Capital. I suppose I must have had something wrong. That's
what led me to the rather long-winded attempt at finding the number of
Capitals and deducting from it to find the Largest. (My test cell was 20
characters long, with a 5th capital in position 16, and a 6th in
position 18)

Copying your version of SMALL() works absolutely fine.

On another point, I had also played around with reducing the formula
length, by using a technique which I first saw used by Harlan Grove. He
used a Named formula of
seq = ROW(INDIRECT("1:1024"))
The problem of course, using a large number like 1024 (or any number
greater than the length of the string being tested), is that CODE() of a
Null returns a #VALUE error.

I could not think of any way of limiting seq to the length of the string
concerned, whilst keeping the named formula as non-cell specific.

If you set the length to be "1:20" (my test cell size) then it works and
your formula would shorten to

=IF(A1="","",SMALL(IF((CODE(MID(A1,seq,1))<91)*(CO DE(MID(A1,seq,1))64),test),5))

and if the parameter of 5 were taken out to a cell e.g. $E$1, then even
with the test to ensure that there are the required number of capitals
to avoid the #NUM error, the formula becomes

=IF(SUM(((CODE(MID(A1,seq,1))<91))*((CODE(MID(A1,s eq,1))64)))<$E$1,"",IF(A1="","",SMALL(IF((CODE(MI D(A1,seq,1))<91)*(CODE(MID(A1,seq,1))64),seq),$E$ 1)))

What I wanted to ask you Tom, is there any way you can think of for
passing the length of the string to the named formula seq?

I can do it if I define scount = LEN(Offset(D1,0,-3)
and seq =ROW(INDIRECT("1:"&Scount))

but then it only works if you put the main formulae in column D, not in
any other column.
Have you any thoughts on this?

--
Regards

Roger Govier


"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