Searching for Capital Letters

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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

What changes in your fabulous formula?

Thank you,

FLKulchar
 
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!!!!
 
Unfortunately, my VBA knowledge is totally nonexistent...can you perform my
query using the EXCEL functions?

Thanks,

FLKulchar
 
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.
 
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
 
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.
 
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.
 
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
 
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
 
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?
 
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.
 
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.
 
VERY good...thank you,

FLKulchar
Tom Ogilvy said:
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.
Click to expand...
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Capital Letter 6
search a string and strip out ONLY the capital letters 3
Removing Sequential Numbers 4
TEXT (searching for a character) 3
VBA Linest function for polynomial regression on horizontal range 0
Change the color of each capital letter in a rich text memo field. 0
How to make First letter of the cell in capital letter 17
DIV/0 6

Back
Top