If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Find the UPPERCASE letter in a string
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Find the UPPERCASE letter in a string

#1
February 7th 09, 04:37 AM posted to microsoft.public.excel.worksheet.functions
 gritgranite external usenet poster Posts: 1
Find the UPPERCASE letter in a string

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!
#2
February 7th 09, 04:38 AM posted to microsoft.public.excel.worksheet.functions
 Teethless mama external usenet poster Posts: 3,718
Find the UPPERCASE letter in a string

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!

#3
February 7th 09, 04:51 AM posted to microsoft.public.excel.worksheet.functions
 Bernie Deitrick external usenet poster Posts: 5,441
Find the UPPERCASE letter in a string

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!

#4
February 7th 09, 06:19 AM posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768
Find the UPPERCASE letter in a string

>a single UPPERCASE character in the string

=LOOKUP(1E100,FIND(D\$2\$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!

#5
February 7th 09, 07:51 AM posted to microsoft.public.excel.worksheet.functions
 Rick Rothstein external usenet poster Posts: 5,934
Find the UPPERCASE letter in a string

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!

#6
February 7th 09, 08:03 AM posted to microsoft.public.excel.worksheet.functions
 Rick Rothstein external usenet poster Posts: 5,934
Find the UPPERCASE letter in a string

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!

>

#7
February 7th 09, 06:21 PM posted to microsoft.public.excel.worksheet.functions
 Shane Devenshire[_2_] external usenet poster Posts: 3,346
Find the UPPERCASE letter in a string

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!

#8
February 7th 09, 06:46 PM posted to microsoft.public.excel.worksheet.functions
 T. Valko external usenet poster Posts: 15,768
Find the UPPERCASE letter in a string

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!

#9
February 7th 09, 09:19 PM posted to microsoft.public.excel.worksheet.functions
 Shane Devenshire[_2_] external usenet poster Posts: 3,346
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!

>
>
>

#10
February 7th 09, 09:25 PM posted to microsoft.public.excel.worksheet.functions
 Shane Devenshire[_2_] external usenet poster Posts: 3,346
Find the UPPERCASE letter in a string

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!

>
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Change 3 letter text string to a number string Pete Excel Discussion (Misc queries) 3 December 31st 07 07:47 PM selecting the last letter in a string csr1176 New Users to Excel 2 November 22nd 07 07:15 PM Count letter string, e.g. h/EL/p Lee Excel Worksheet Functions 16 September 4th 07 11:57 AM Trying to FIND lowercase or uppercase of target occurence u473 Excel Worksheet Functions 3 August 23rd 07 11:08 PM Parse data where break is a first uppercase character in a string? Glen Excel Worksheet Functions 5 April 16th 06 07:28 PM

All times are GMT +1. The time now is 03:49 AM.