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. 


Thread Tools  Display Modes 
#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 AZ. 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




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 AZ. 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




Find the UPPERCASE letter in a string
Array enter (enter using CtrlShiftEnter)
=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 AZ. 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




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 AZ. 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




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 AZ. 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




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 AZ. 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




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 AZ. 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




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 AZ. 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




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 AZ. 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




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 AZ. 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  


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 