Find the UPPERCASE letter in a string

[gritgranite]

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!

 

[Teethless mama]

Create a helper column D
D2: holds A
D3: holds B
D4: holds C
....D27 holds Z

=FIND(INDEX($D$2:$D$27,MATCH(1,--ISNUMBER(FIND($D$2:$D$27,C2)),)),C2)

Ctrl+Shift+Enter, not just Enter

 

[Bernie Deitrick]

Array enter (enter using Ctrl-Shift-Enter)

=MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT("65:90"))),C2)),"",FIND(CHAR(ROW(INDIRECT("65:90"))),C2)))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

a single UPPERCASE character in the string

=LOOKUP(1E100,FIND(D$2$27,C2))

 

[Rick Rothstein]

Unlike Teethless mama's offering, this formula...

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*ROW(INDIRECT("A$1:A"&LEN(A1))))

doesn't require a helper column. In addition, although not as compact as
Bernie's formula, it does not require Ctrl+Shift+Enter to commit it (just
use the Enter key).

 

[Rick Rothstein]

Here is another normally entered formula to do what the OP asked...

=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

 

[Shane Devenshire]

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)

 

[T. Valko]

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.

 

[Shane Devenshire]

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

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

both array entered.

 

[Shane Devenshire]

And I see I'm having an off day, even the last one doesn't work.

Yuk!!!!