On Thu, 28 Sep 2006 01:13:01 -0700, F. Lawrence Kulchar
wrote:

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