| 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
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#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
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 |
|
#11
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 |
| 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 | |||
Hybrid Mode
