(A7) value = JohnWinstonLennon
These Excel worksheet equations work just fine on the base Excel Worksheet. Both equations give the location for the 2nd and 3rd Capital Letters in a run-on String, such as the one above.
I am trying to write them into a function with VBA, but I cannot figure out how to make the Indirect reference work in the function. Any Help, Please!
If you trying to write a function using VBA, then maybe give the following a try:
Sub Test() Dim str As String: str = "JohnWinstonLennon" With CreateObject("vbscript.regexp") .Global = True .Pattern = "[A-Z]" If .Test(str) Then Set matches = .Execute(str) For Each Match In matches Debug.Print Match.FirstIndex + 1 Next End If End With End Sub
You could rework this into an UDF to return the position of the nth capital?