Searching for Capital Letters

[Guest]

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

 

[Roger Govier]

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.

 

[Roger Govier]

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

 

[NickHK]

Here's one way :
Public Function FirstCapitalLetter(argStr As Variant, Optional StartAt As
Long = 1) As Long
Dim LowerCase As String
Dim i As Long

LowerCase = LCase(argStr)

For i = StartAt To Len(LowerCase)
If Mid(LowerCase, i, 1) <> Mid(argStr, i, 1) Then
FirstCapitalLetter = i
Exit Function
End If
Next

FirstCapitalLetter = 0
End Function

Seems OK. Not sure how all characters are handled in non-English text
though. You'd need to test.

NickHK

 

[FLKulchar]

GREAT...

Now, suppose I want to test for the 5th capital letter??

What changes in your fabulous formula?

Thank you,

FLKulchar

 

[Roger Govier]

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

 

[FLKulchar]

Unfortunately, my VBA knowledge is totally nonexistent...can you perform my
query using the EXCEL functions?

Thanks,

FLKulchar

 

[Roger Govier]

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.

 

[NickHK]

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

 

[Guest]

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.

 

[Roger Govier]

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.

 

[bplumhoff]

Hi Roger,

A1: TEXT
B1: NUMBER

then

=MATCH(B1,MMULT(TRANSPOSE(1-(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=CODE(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)))),--(ROW(INDIRECT("1:"&LEN(A1)))-1+TRANSPOSE(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))<=LEN(A1)-1)),)

[array-entered] returns the position of the NUMBER-th capital letter in
TEXT.

Nick's approach - which I followed - works also for German "Umlaute".

Have fun,
Bernd

 

[NickHK]

Roger,
I'm the other way ; avoid formulae in favour of functions.

NickHK

 

[Guest]

Hello,

Or
=SMALL(IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>CODE(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),B1)
array-entered. Put 5 into cell B1.

Regards,
Bernd

 

[Roger Govier]

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)*(CODE(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,seq,1))>64)))<$E$1,"",IF(A1="","",SMALL(IF((CODE(MID(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?

 

[Guest]

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.

 

[Roger Govier]

Hi Bernd

What a thread the OP has started.
Thank you for both of your postings. The former is going to take a bit
of "grey matter" disturbance to figure out as I have never used the
MMULT function before. The result is fine.

The latter I prefer, and is a very neat solution comparing Upper and
Lower case of the same character, much like Nick was doing in his UDF.

 

[Roger Govier]

Hi Tom

Great! I like it. Thank you very much.

 

[FLKulchar]

VERY good...thank you,

FLKulchar

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.

 

[bplumhoff]

Hi Roger,

Please forget my MMULT approach. Tom's SMALL combined with Nick's LCASE
or LOWER was it.

MMULT was part of a complicated way to construct {0,0,1,0,2,0,0,3,..}
to get the order of capitals.

But maybe there will be a more elegant use for these matrices {1,1;0,1}
or {1,1,1;0,1,1;0,0,1} in future

Nick's way of comparing UPPER and LOWER will save the day for
Scandinavians, Germans, etc.

Regards,
Bernd