Home 
Search 
Today's 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 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! 
#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! 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Change 3 letter text string to a number string  Excel Discussion (Misc queries)  
selecting the last letter in a string  New Users to Excel  
Count letter string, e.g. h/EL/p  Excel Worksheet Functions  
Trying to FIND lowercase or uppercase of target occurence  Excel Worksheet Functions  
Parse data where break is a first uppercase character in a string?  Excel Worksheet Functions 