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!

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


"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!

a single UPPERCASE character in the string

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

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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


"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!

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


"gritgranite" wrote in message
...
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!

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

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$1 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*RO W(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 (MVP - Excel)


"gritgranite" wrote in message
...
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!

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" ))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Unlike Teethless mama's offering, this formula...

=SUMPRODUCT(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("A$1 :A"&LEN(A1))),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))*RO W(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 (MVP - Excel)


"gritgranite" wrote in message
...
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!

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!

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!

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!

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

Yuk!!!!
--
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!

1. To find the position of the UPPERCASE character in a string, you can use the following formula:
   
   Code:

   =MATCH(TRUE,ISNUMBER(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-64),0)

   Here, A1 is the cell containing the string you want to search for the UPPERCASE character.
   
   Let's break down the formula:
   1. MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) extracts each character in the string one by one, starting from the first character.
   2. CODE(...) returns the ASCII code of each character.
   3. ISNUMBER(...-64) checks if the ASCII code minus 64 (the ASCII code for 'A') is a number. If it is, then the character is an UPPERCASE letter.
   4. MATCH(TRUE,...,0) returns the position of the first TRUE value in the array. This corresponds to the position of the UPPERCASE letter in the string.
   Simply copy and paste the formula into the cell where you want to display the position of the UPPERCASE letter, and replace A1 with the cell containing the string you want to search. You can then drag the formula down to apply it to all the cells you want to search.