Searching for Capital Letters

[FLKulchar]

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

 

[Leo Heuser]

..

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

Hello FLKulchar

For a formula solution, which also works for local alphabets, try this one:

=SMALL(IF(EXACT(PROPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),1)

This is an array formula, which must be entered with
<Shift><Ctrl><Enter> instead of just <Enter>

 

[Roger Govier]

Hi Bernard

I agree absolutely, but nonetheless I will take the time to investigate
the use of MMULT for other occasions.
Thank you for drawing it to my attention.

 

[NickHK]

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

 

[NickHK]

Bernd,
I'm not sure how all languages will deal with such comparison, but between
all the posted code, I'm sure the OP can contruct something workable.

NickHK

 

[bplumhoff]

Hi Leo,

Interesting. Works even for Greek and Cyrillic alphabet.

But you are misleading the poor user I would take UPPER instead of
PROPER ...

Have fun,
Bernd

 

[Ron Rosenfeld]

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

Long Thread!

Another approach would be to use "Regular Expressions"

As a worksheet formula, assuming the strings are less than 256 characters long,
you could download and install Longre's free morefunc.xll add-in from:
http://xcell05.free.fr

and use the formula:

=REGEX.FIND(A1,"[A-Z]",1)

In the formula, the last argument (1), which is optional, is the index number.
So 1 would find the first Capital Letter, 2 the second, and so forth. If the
FIND comes up empty, it returns a zero (0).

If the strings might be longer than 255, an equivalent function can be written
using VBA. There are also ways of making the regular expression sensitive to
the various accented characters present in non-English languages, should that
be necessary.


--ron

 

[Leo Heuser]

Hi Bernd

Hi Leo,

Interesting. Works even for Greek and Cyrillic alphabet.

But you are misleading the poor user I would take UPPER instead of
PROPER ...

So would I

The Danish name for UPPER() is STORE.BOGSTAVER() and for
PROPER() it's STORT.FORBOGSTAV. Easy to pick the wrong
one, especially in a situation where each one will do, and I didn't
pay attention to PROPER(), when the formula was translated.

I'm certain that the poor user now knows, that UPPER would have been
the PROPER function

Regards
Leo Heuser