A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Find the UPPERCASE letter in a string



 
 
Thread Tools Display Modes
  #1  
Old February 7th 09, 05:37 AM posted to microsoft.public.excel.worksheet.functions
gritgranite
external usenet poster
 
Posts: 1
Default 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!
Ads
  #2  
Old February 7th 09, 05:38 AM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,718
Default 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  
Old February 7th 09, 05:51 AM posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
external usenet poster
 
Posts: 5,444
Default 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  
Old February 7th 09, 07:19 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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  
Old February 7th 09, 08:51 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default 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  
Old February 7th 09, 09:03 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default 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  
Old February 7th 09, 07:21 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default 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  
Old February 7th 09, 07:46 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,768
Default 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  
Old February 7th 09, 10:19 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
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!

>
>
>

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

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

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 08:47 PM
selecting the last letter in a string csr1176 New Users to Excel 2 November 22nd 07 08: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 02:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.