View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_] Shane Devenshire[_2_] is offline
external usenet poster
 
Posts: 3,346
Default Find the UPPERCASE letter in a string

In spite of all the caveats, my formula is wrong! It should be

=MAX(IF(ISERR(FIND(CHAR(ROW(65:90)),C2)),"",FIND(C HAR(ROW(65:90)),C2)))
or
=MATCH(TRUE,ISNUMBER(FIND(CHAR(ROW(65:90)),C2)),)

both array entered.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"T. Valko" wrote:

Caveat...when using expressions like ROW(65:90).

This is vulnerable to row insertions. If you inserted a new row 1 for
whatever reason then the formula would change to ROW(66:91) and now you
would miss char 65 (A).

Using INDIRECT prevents this from happening *but* then the formula would now
be volatile.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

=IF(ISERR(FIND(CHAR(ROW(65:90)),A1)),"",FIND(CHAR( ROW(65:90)),A1))

or if you know there is always a single uppercase letter in the string

=FIND(CHAR(ROW(65:90)),C2)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"gritgranite" wrote:

The text string in my cell consists of lowercase alpha and numeric
characters
with the exception of a single UPPERCASE character in the string. The
UPPERCASE character can be in the range A-Z. How can i find the position
of
this UPPERCASE character in the string?

e.g. cell C2 contains "abc2defGhi3j" - i need to find the position of 'G'
cell C3 contains "abC2defghi3j" - i need to find the position of
'C'
and so on for 25k cells

thanks!