| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for Capital Letters
How do I find the position of the FIRST...(or fifth) capital letter within a
text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi
Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi
Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Here's one way :
Public Function FirstCapitalLetter(argStr As Variant, Optional StartAt As Long = 1) As Long Dim LowerCase As String Dim i As Long LowerCase = LCase(argStr) For i = StartAt To Len(LowerCase) If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then FirstCapitalLetter = i Exit Function End If Next FirstCapitalLetter = 0 End Function Seems OK. Not sure how all characters are handled in non-English text though. You'd need to test. NickHK "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
GREAT...
Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi
Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Unfortunately, my VBA knowledge is totally nonexistent...can you perform my
query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi
are you sure you're ready fro this<vbg {=IF(A2="","", LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))* ROW(INDIRECT("1:"&LEN(A2))), SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))} Again array entered with CSE. the part SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) entered as an array formula, counts the number of CAPITAL letters in the string I made the last part +1-5 as the 5 is the 5th capital letter and is therefore easier to see than using a net 4. To prevent errors with strings less than 5 capital letters, you should really use this again at the beginning of the formula to provide an additional IF test, to give a null value in this scenario. -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Roger,
Your formula seems to give the correct answer, but for me, it's for situations like this that god gave us UDFs. To the OP ; From the worksheet, press Alt+F11to open the VBE. Right-click on the the entries your see for your current workbook, something like "Sheet1" I suppose. Select New Module. Paste the code below in this new module. Then, from a worksheet cell, enter "=GetCapitalLetterPosition(A2,1,5)" where A2 is the address of the word you want to examine. NickHK Public Function GetCapitalLetterPosition(argStr As Variant, Optional StartAtPosition As Long = 1, Optional Occurence As Long = 1) As Long Dim LowerCase As String Dim i As Long Dim OccurenceCount As Long LowerCase = LCase(argStr) For i = StartAtPosition To Len(LowerCase) If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then OccurenceCount = OccurenceCount + 1 If OccurenceCount = Occurence Then GetCapitalLetterPosition = i Exit Function End If End If Next GetCapitalLetterPosition = 0 End Function "Roger Govier" bl... Hi are you sure you're ready fro this<vbg {=IF(A2="","", LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))* ROW(INDIRECT("1:"&LEN(A2))), SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))} Again array entered with CSE. the part SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) entered as an array formula, counts the number of CAPITAL letters in the string I made the last part +1-5 as the 5 is the 5th capital letter and is therefore easier to see than using a net 4. To prevent errors with strings less than 5 capital letters, you should really use this again at the beginning of the formula to provide an additional IF test, to give a null value in this scenario. -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
think Roger was closer on his first attempt:
=IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 )) entered with CTrl+Shift+Enter. Change the 5 at the end of the above formula to indicate which Capital letter position you want. Change it to a 1, you get the first. It will display an error value if you don't have any or have less than you ask for. -- Regards, Tom Ogilvy "FLKulchar" wrote: Unfortunately, my VBA knowledge is totally nonexistent...can you perform my query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Nick
I agree - but I have never been good at writing functions, perhaps I should practice some more!! To the OP's second post, I did give some VBA code first, but he asked for a formula solution. Your UDF works absolutely fine and I will use this as a guide to trying to write a few more for me to play with. Thank you. -- Regards Roger Govier "NickHK" wrote in message ... Roger, Your formula seems to give the correct answer, but for me, it's for situations like this that god gave us UDFs. To the OP ; From the worksheet, press Alt+F11to open the VBE. Right-click on the the entries your see for your current workbook, something like "Sheet1" I suppose. Select New Module. Paste the code below in this new module. Then, from a worksheet cell, enter "=GetCapitalLetterPosition(A2,1,5)" where A2 is the address of the word you want to examine. NickHK Public Function GetCapitalLetterPosition(argStr As Variant, Optional StartAtPosition As Long = 1, Optional Occurence As Long = 1) As Long Dim LowerCase As String Dim i As Long Dim OccurenceCount As Long LowerCase = LCase(argStr) For i = StartAtPosition To Len(LowerCase) If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then OccurenceCount = OccurenceCount + 1 If OccurenceCount = Occurence Then GetCapitalLetterPosition = i Exit Function End If End If Next GetCapitalLetterPosition = 0 End Function "Roger Govier" bl... Hi are you sure you're ready fro this<vbg {=IF(A2="","", LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))* ROW(INDIRECT("1:"&LEN(A2))), SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))} Again array entered with CSE. the part SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) entered as an array formula, counts the number of CAPITAL letters in the string I made the last part +1-5 as the 5 is the 5th capital letter and is therefore easier to see than using a net 4. To prevent errors with strings less than 5 capital letters, you should really use this again at the beginning of the formula to provide an additional IF test, to give a null value in this scenario. -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Roger,
A1: TEXT B1: NUMBER then =MATCH(B1,MMULT(TRANSPOSE(1-(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=CODE( MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)))),--(ROW(INDIRECT("1:"&LEN(A1)))-1+TRANSPOSE(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))<=LEN(A1)-1)),) [array-entered] returns the position of the NUMBER-th capital letter in TEXT. Nick's approach - which I followed - works also for German "Umlaute". Have fun, Bernd |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Roger,
I'm the other way ; avoid formulae in favour of functions. NickHK "Roger Govier" bl... Hi Nick I agree - but I have never been good at writing functions, perhaps I should practice some more!! To the OP's second post, I did give some VBA code first, but he asked for a formula solution. Your UDF works absolutely fine and I will use this as a guide to trying to write a few more for me to play with. Thank you. -- Regards Roger Govier "NickHK" wrote in message ... Roger, Your formula seems to give the correct answer, but for me, it's for situations like this that god gave us UDFs. To the OP ; From the worksheet, press Alt+F11to open the VBE. Right-click on the the entries your see for your current workbook, something like "Sheet1" I suppose. Select New Module. Paste the code below in this new module. Then, from a worksheet cell, enter "=GetCapitalLetterPosition(A2,1,5)" where A2 is the address of the word you want to examine. NickHK Public Function GetCapitalLetterPosition(argStr As Variant, Optional StartAtPosition As Long = 1, Optional Occurence As Long = 1) As Long Dim LowerCase As String Dim i As Long Dim OccurenceCount As Long LowerCase = LCase(argStr) For i = StartAtPosition To Len(LowerCase) If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then OccurenceCount = OccurenceCount + 1 If OccurenceCount = Occurence Then GetCapitalLetterPosition = i Exit Function End If End If Next GetCapitalLetterPosition = 0 End Function "Roger Govier" bl... Hi are you sure you're ready fro this<vbg {=IF(A2="","", LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))* ROW(INDIRECT("1:"&LEN(A2))), SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))} Again array entered with CSE. the part SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) entered as an array formula, counts the number of CAPITAL letters in the string I made the last part +1-5 as the 5 is the 5th capital letter and is therefore easier to see than using a net 4. To prevent errors with strings less than 5 capital letters, you should really use this again at the beginning of the formula to provide an additional IF test, to give a null value in this scenario. -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hello,
Or =SMALL(IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1))<CODE(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1)) ),1)),ROW(INDIRECT("1:"&LEN(A1)))),B1) array-entered. Put 5 into cell B1. Regards, Bernd |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Tom
Thank you for that. I had attempted using SMALL( ,5) first but came up with the position of the first Capital. I suppose I must have had something wrong. That's what led me to the rather long-winded attempt at finding the number of Capitals and deducting from it to find the Largest. (My test cell was 20 characters long, with a 5th capital in position 16, and a 6th in position 18) Copying your version of SMALL() works absolutely fine. On another point, I had also played around with reducing the formula length, by using a technique which I first saw used by Harlan Grove. He used a Named formula of seq = ROW(INDIRECT("1:1024")) The problem of course, using a large number like 1024 (or any number greater than the length of the string being tested), is that CODE() of a Null returns a #VALUE error. I could not think of any way of limiting seq to the length of the string concerned, whilst keeping the named formula as non-cell specific. If you set the length to be "1:20" (my test cell size) then it works and your formula would shorten to =IF(A1="","",SMALL(IF((CODE(MID(A1,seq,1))<91)*(CO DE(MID(A1,seq,1))64),test),5)) and if the parameter of 5 were taken out to a cell e.g. $E$1, then even with the test to ensure that there are the required number of capitals to avoid the #NUM error, the formula becomes =IF(SUM(((CODE(MID(A1,seq,1))<91))*((CODE(MID(A1,s eq,1))64)))<$E$1,"",IF(A1="","",SMALL(IF((CODE(MI D(A1,seq,1))<91)*(CODE(MID(A1,seq,1))64),seq),$E$ 1))) What I wanted to ask you Tom, is there any way you can think of for passing the length of the string to the named formula seq? I can do it if I define scount = LEN(Offset(D1,0,-3) and seq =ROW(INDIRECT("1:"&Scount)) but then it only works if you put the main formulae in column D, not in any other column. Have you any thoughts on this? -- Regards Roger Govier "Tom Ogilvy" wrote in message ... think Roger was closer on his first attempt: =IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 )) entered with CTrl+Shift+Enter. Change the 5 at the end of the above formula to indicate which Capital letter position you want. Change it to a 1, you get the first. It will display an error value if you don't have any or have less than you ask for. -- Regards, Tom Ogilvy "FLKulchar" wrote: Unfortunately, my VBA knowledge is totally nonexistent...can you perform my query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Using Bernd's formula (an approach I considered but abandoned because of
numbers in the string, but he did it the right way). You could do something like this: =SMALL(IF(CODE(MID(A1&REPT(" ",30),ROW(INDIRECT("1:30")),1))<CODE(MID(LOWER(A1 )&REPT(" ",30),ROW(INDIRECT("1:30")),1)),ROW(1:30)),B1) then it becomes =SMALL(IF(CODE(MID(A1&REPT(" ",30),seq,1))<CODE(MID(LOWER(A1)&REPT(" ",30),seq,1)),ROW(1:30)),B1) where seq is defined as ROW(INDIRECT("1:30")) There may be a cleverer way, but nothing jumped to mind. -- Regards, Tom Ogilvy "Roger Govier" wrote: Hi Tom Thank you for that. I had attempted using SMALL( ,5) first but came up with the position of the first Capital. I suppose I must have had something wrong. That's what led me to the rather long-winded attempt at finding the number of Capitals and deducting from it to find the Largest. (My test cell was 20 characters long, with a 5th capital in position 16, and a 6th in position 18) Copying your version of SMALL() works absolutely fine. On another point, I had also played around with reducing the formula length, by using a technique which I first saw used by Harlan Grove. He used a Named formula of seq = ROW(INDIRECT("1:1024")) The problem of course, using a large number like 1024 (or any number greater than the length of the string being tested), is that CODE() of a Null returns a #VALUE error. I could not think of any way of limiting seq to the length of the string concerned, whilst keeping the named formula as non-cell specific. If you set the length to be "1:20" (my test cell size) then it works and your formula would shorten to =IF(A1="","",SMALL(IF((CODE(MID(A1,seq,1))<91)*(CO DE(MID(A1,seq,1))64),test),5)) and if the parameter of 5 were taken out to a cell e.g. $E$1, then even with the test to ensure that there are the required number of capitals to avoid the #NUM error, the formula becomes =IF(SUM(((CODE(MID(A1,seq,1))<91))*((CODE(MID(A1,s eq,1))64)))<$E$1,"",IF(A1="","",SMALL(IF((CODE(MI D(A1,seq,1))<91)*(CODE(MID(A1,seq,1))64),seq),$E$ 1))) What I wanted to ask you Tom, is there any way you can think of for passing the length of the string to the named formula seq? I can do it if I define scount = LEN(Offset(D1,0,-3) and seq =ROW(INDIRECT("1:"&Scount)) but then it only works if you put the main formulae in column D, not in any other column. Have you any thoughts on this? -- Regards Roger Govier "Tom Ogilvy" wrote in message ... think Roger was closer on his first attempt: =IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 )) entered with CTrl+Shift+Enter. Change the 5 at the end of the above formula to indicate which Capital letter position you want. Change it to a 1, you get the first. It will display an error value if you don't have any or have less than you ask for. -- Regards, Tom Ogilvy "FLKulchar" wrote: Unfortunately, my VBA knowledge is totally nonexistent...can you perform my query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Bernd
What a thread the OP has started. Thank you for both of your postings. The former is going to take a bit of "grey matter" disturbance to figure out as I have never used the MMULT function before. The result is fine. The latter I prefer, and is a very neat solution comparing Upper and Lower case of the same character, much like Nick was doing in his UDF. -- Regards Roger Govier "Bernd Plumhoff" <reverse.of.moc.liborplus@liam wrote in message ... Hello, Or =SMALL(IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))), 1))<CODE(MID(LOWER(A1),ROW(INDIRECT("1:"&LEN(A1)) ),1)),ROW(INDIRECT("1:"&LEN(A1)))),B1) array-entered. Put 5 into cell B1. Regards, Bernd |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Tom
Great! I like it. Thank you very much. -- Regards Roger Govier "Tom Ogilvy" wrote in message ... Using Bernd's formula (an approach I considered but abandoned because of numbers in the string, but he did it the right way). You could do something like this: =SMALL(IF(CODE(MID(A1&REPT(" ",30),ROW(INDIRECT("1:30")),1))<CODE(MID(LOWER(A1 )&REPT(" ",30),ROW(INDIRECT("1:30")),1)),ROW(1:30)),B1) then it becomes =SMALL(IF(CODE(MID(A1&REPT(" ",30),seq,1))<CODE(MID(LOWER(A1)&REPT(" ",30),seq,1)),ROW(1:30)),B1) where seq is defined as ROW(INDIRECT("1:30")) There may be a cleverer way, but nothing jumped to mind. -- Regards, Tom Ogilvy "Roger Govier" wrote: Hi Tom Thank you for that. I had attempted using SMALL( ,5) first but came up with the position of the first Capital. I suppose I must have had something wrong. That's what led me to the rather long-winded attempt at finding the number of Capitals and deducting from it to find the Largest. (My test cell was 20 characters long, with a 5th capital in position 16, and a 6th in position 18) Copying your version of SMALL() works absolutely fine. On another point, I had also played around with reducing the formula length, by using a technique which I first saw used by Harlan Grove. He used a Named formula of seq = ROW(INDIRECT("1:1024")) The problem of course, using a large number like 1024 (or any number greater than the length of the string being tested), is that CODE() of a Null returns a #VALUE error. I could not think of any way of limiting seq to the length of the string concerned, whilst keeping the named formula as non-cell specific. If you set the length to be "1:20" (my test cell size) then it works and your formula would shorten to =IF(A1="","",SMALL(IF((CODE(MID(A1,seq,1))<91)*(CO DE(MID(A1,seq,1))64),test),5)) and if the parameter of 5 were taken out to a cell e.g. $E$1, then even with the test to ensure that there are the required number of capitals to avoid the #NUM error, the formula becomes =IF(SUM(((CODE(MID(A1,seq,1))<91))*((CODE(MID(A1,s eq,1))64)))<$E$1,"",IF(A1="","",SMALL(IF((CODE(MI D(A1,seq,1))<91)*(CODE(MID(A1,seq,1))64),seq),$E$ 1))) What I wanted to ask you Tom, is there any way you can think of for passing the length of the string to the named formula seq? I can do it if I define scount = LEN(Offset(D1,0,-3) and seq =ROW(INDIRECT("1:"&Scount)) but then it only works if you put the main formulae in column D, not in any other column. Have you any thoughts on this? -- Regards Roger Govier "Tom Ogilvy" wrote in message ... think Roger was closer on his first attempt: =IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 )) entered with CTrl+Shift+Enter. Change the 5 at the end of the above formula to indicate which Capital letter position you want. Change it to a 1, you get the first. It will display an error value if you don't have any or have less than you ask for. -- Regards, Tom Ogilvy "FLKulchar" wrote: Unfortunately, my VBA knowledge is totally nonexistent...can you perform my query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
VERY good...thank you,
FLKulchar "Tom Ogilvy" wrote in message ... think Roger was closer on his first attempt: =IF(A1="","",SMALL(IF((CODE(MID(A1,ROW(INDIRECT("1 :"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("1: "&LEN(A1))),1))64),ROW(INDIRECT("1:"&LEN(A1)))),5 )) entered with CTrl+Shift+Enter. Change the 5 at the end of the above formula to indicate which Capital letter position you want. Change it to a 1, you get the first. It will display an error value if you don't have any or have less than you ask for. -- Regards, Tom Ogilvy "FLKulchar" wrote: Unfortunately, my VBA knowledge is totally nonexistent...can you perform my query using the EXCEL functions? Thanks, FLKulchar "Roger Govier" wrote in message ... Hi Hmmmm..... Easiest with the VBA solution Sub test5() Dim i As Long, j As Long Dim s As String, c As String s = ActiveCell.Value j = 0 For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then j = j + 1 If j = 5 Then MsgBox "Fifth Uppercase Alpha Position is " & i Exit Sub End If End If Next MsgBox "No Fifth Uppercase Alpha in " & s End Sub I will need to give the formula solution a little more thought!!!! -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Roger,
Please forget my MMULT approach. Tom's SMALL combined with Nick's LCASE or LOWER was it. MMULT was part of a complicated way to construct {0,0,1,0,2,0,0,3,..} to get the order of capitals. But maybe there will be a more elegant use for these matrices {1,1;0,1} or {1,1,1;0,1,1;0,0,1} in future :-) Nick's way of comparing UPPER and LOWER will save the day for Scandinavians, Germans, etc. Regards, Bernd |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hello:
All I know is that the formulae work...I am F. L. Kulchar...I started the queries. I want to thank all of you for your unbelievably intelligent input...I wish I could be "near" you so I could learn more. FLKulchar "NickHK" wrote in message ... Roger, I'm the other way ; avoid formulae in favour of functions. NickHK "Roger Govier" bl... Hi Nick I agree - but I have never been good at writing functions, perhaps I should practice some more!! To the OP's second post, I did give some VBA code first, but he asked for a formula solution. Your UDF works absolutely fine and I will use this as a guide to trying to write a few more for me to play with. Thank you. -- Regards Roger Govier "NickHK" wrote in message ... Roger, Your formula seems to give the correct answer, but for me, it's for situations like this that god gave us UDFs. To the OP ; From the worksheet, press Alt+F11to open the VBE. Right-click on the the entries your see for your current workbook, something like "Sheet1" I suppose. Select New Module. Paste the code below in this new module. Then, from a worksheet cell, enter "=GetCapitalLetterPosition(A2,1,5)" where A2 is the address of the word you want to examine. NickHK Public Function GetCapitalLetterPosition(argStr As Variant, Optional StartAtPosition As Long = 1, Optional Occurence As Long = 1) As Long Dim LowerCase As String Dim i As Long Dim OccurenceCount As Long LowerCase = LCase(argStr) For i = StartAtPosition To Len(LowerCase) If Mid(LowerCase, i, 1) < Mid(argStr, i, 1) Then OccurenceCount = OccurenceCount + 1 If OccurenceCount = Occurence Then GetCapitalLetterPosition = i Exit Function End If End If Next GetCapitalLetterPosition = 0 End Function "Roger Govier" bl... Hi are you sure you're ready fro this<vbg {=IF(A2="","", LARGE((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))* ROW(INDIRECT("1:"&LEN(A2))), SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) +1-5))} Again array entered with CSE. the part SUM((--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<91))* (--(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))64))) entered as an array formula, counts the number of CAPITAL letters in the string I made the last part +1-5 as the 5 is the 5th capital letter and is therefore easier to see than using a net 4. To prevent errors with strings less than 5 capital letters, you should really use this again at the beginning of the formula to provide an additional IF test, to give a null value in this scenario. -- Regards Roger Govier "FLKulchar" wrote in message ... GREAT... Now, suppose I want to test for the 5th capital letter?? What changes in your fabulous formula? Thank you, FLKulchar "Roger Govier" wrote in message ... Hi Just realised you asked in .programming so you probably wanted a code solution not a formula. Something like the following should get you started. Sub test() Dim i As Long Dim s As String, c As String s = ActiveCell.Value For i = 1 To Len(s) c = Mid(s, i, 1) If Asc(c) 64 And Asc(c) < 91 Then MsgBox "First Uppercase Alpha Position is " & i Exit Sub End If Next End Sub -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Rather unwieldy, but seems to produce the desired result, the array entered {=IF(A1="","", SUM((--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))* (--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))64))* ROW(INDIRECT("1:"&LEN(A1)))))} Commit and edit with Ctrl+Shift+Enter (CSE) not just Enter. Excel will insert the curly braces { } when you use CSE, do not type them yourself. -- Regards Roger Govier "F. Lawrence Kulchar" wrote in message ... How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
..
"FLKulchar" skrev i en meddelelse ... Hello: All I know is that the formulae work...I am F. L. Kulchar...I started the queries. I want to thank all of you for your unbelievably intelligent input...I wish I could be "near" you so I could learn more. FLKulchar Hello FLKulchar For a formula solution, which also works for local alphabets, try this one: =SMALL(IF(EXACT(PROPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)), MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIREC T("1:"&LEN(A1)))),1) This is an array formula, which must be entered with <Shift<Ctrl<Enter instead of just <Enter -- Best regards Leo Heuser Followup to newsgroup only please |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Bernard
I agree absolutely, but nonetheless I will take the time to investigate the use of MMULT for other occasions. Thank you for drawing it to my attention. -- Regards Roger Govier wrote in message ups.com... Hi Roger, Please forget my MMULT approach. Tom's SMALL combined with Nick's LCASE or LOWER was it. MMULT was part of a complicated way to construct {0,0,1,0,2,0,0,3,..} to get the order of capitals. But maybe there will be a more elegant use for these matrices {1,1;0,1} or {1,1,1;0,1,1;0,0,1} in future :-) Nick's way of comparing UPPER and LOWER will save the day for Scandinavians, Germans, etc. Regards, Bernd |
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
groups.com... Hi Roger, Please forget my MMULT approach. Tom's SMALL combined with Nick's LCASE or LOWER was it. MMULT was part of a complicated way to construct {0,0,1,0,2,0,0,3,..} to get the order of capitals. But maybe there will be a more elegant use for these matrices {1,1;0,1} or {1,1,1;0,1,1;0,0,1} in future :-) Nick's way of comparing UPPER and LOWER will save the day for Scandinavians, Germans, etc. Regards, Bernd |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Bernd,
I'm not sure how all languages will deal with such comparison, but between all the posted code, I'm sure the OP can contruct something workable. NickHK groups.com... Hi Roger, Please forget my MMULT approach. Tom's SMALL combined with Nick's LCASE or LOWER was it. MMULT was part of a complicated way to construct {0,0,1,0,2,0,0,3,..} to get the order of capitals. But maybe there will be a more elegant use for these matrices {1,1;0,1} or {1,1,1;0,1,1;0,0,1} in future :-) Nick's way of comparing UPPER and LOWER will save the day for Scandinavians, Germans, etc. Regards, Bernd |
|
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Leo,
Interesting. Works even for Greek and Cyrillic alphabet. But you are misleading the poor user :-) I would take UPPER instead of PROPER ... Have fun, Bernd |
|
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
On Thu, 28 Sep 2006 01:13:01 -0700, F. Lawrence Kulchar
wrote: How do I find the position of the FIRST...(or fifth) capital letter within a text string? EG: A 1 abndHnmJ 2 123wTTghy6TH 3 hhhHhhh ANSWER: A1 would be 5 A2 would be 5 A3 would be 4 HOW TO TEST FOR THIS?? Thank you, FLKulchar Long Thread! Another approach would be to use "Regular Expressions" As a worksheet formula, assuming the strings are less than 256 characters long, you could download and install Longre's free morefunc.xll add-in from: http://xcell05.free.fr and use the formula: =REGEX.FIND(A1,"[A-Z]",1) In the formula, the last argument (1), which is optional, is the index number. So 1 would find the first Capital Letter, 2 the second, and so forth. If the FIND comes up empty, it returns a zero (0). If the strings might be longer than 255, an equivalent function can be written using VBA. There are also ways of making the regular expression sensitive to the various accented characters present in non-English languages, should that be necessary. --ron |
|
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Searching for Capital Letters
Hi Bernd
skrev i en meddelelse oups.com... Hi Leo, Interesting. Works even for Greek and Cyrillic alphabet. But you are misleading the poor user :-) I would take UPPER instead of PROPER ... So would I :-) The Danish name for UPPER() is STORE.BOGSTAVER() and for PROPER() it's STORT.FORBOGSTAV. Easy to pick the wrong one, especially in a situation where each one will do, and I didn't pay attention to PROPER(), when the formula was translated. I'm certain that the poor user now knows, that UPPER would have been the PROPER function :-) Regards Leo Heuser |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to change small letters to capital letters | Excel Discussion (Misc queries) | |||
| how do i turn all letters into capital letters? | Excel Discussion (Misc queries) | |||
| Capital Letters Only | Excel Discussion (Misc queries) | |||
| Capital Letters | Excel Programming | |||
| Capital Letters | Excel Worksheet Functions | |||
Linear Mode
