Find Position of First Capital Letter in a String
There has to be a better way than this, but I can’t think of it. I have a string with a capital letter somewhere in it and I need to determine the position of that letter. The goal is to strip out all the letters before it. The test subject is the string mdtTxnDate. The array formula that seems to work is:
=MATCH(1,(CODE(MID(J13,ROW($A$1:$A$255),1))< =90)*(CODE(MID(J13,ROW($A$1:$A$255),1))>=65),FALSE)
It assumes that the capital letter won’t be more than 255 characters in, which is true in my case. This is an array formula, so it needs to be entered with Control+Shift+Enter, not just enter. Here’s the break down:
CODE(MID(J13,ROW($A$1:$A$255),1)) returns an array of ASCII characters that make up the string. Although it returns a 255 element array, I’m only going to show 10 elements because that’s how long my string is. For my test subject, this portion of the formula will return
{109;100;116;84;120;110;68;97;116;101}
Those are the ASCII codes for mdtTxnDate.
CODE(MID(J13,ROW($A$1:$A$255),1))< =90 returns an array of TRUEs and FALSEs based on whether or not the ASCII code is less than or equal to 90. Ninety is the ASCII code for capital Z. This will return:
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
I can see that the fourth letter has an ASCII code less than 90. There is a similar section of the formula that does the same test except that it checks for ASCII codes greater than or equal to 65 (capital A). It returns a similar array which is then multiplied by the first array. In Excel formulas, FALSE is equivalent to zero and TRUE is equivalent to one. When you multiply these two arrays, you get an array with ones and zeros. The ones mean that there was a TRUE in that same spot in both arrays. If there had been a FALSE in either array, it would have returned zero. The resulting array looks like this:
{0;0;0;1;0;0;1;0;0;0}
It looks like positions four and seven are my capital letters. Now I use the MATCH function to find the first 1 in the array and the formula returns 4.
Now I know you guys can come up with something better and I also know you’ll want to share it. If you do, please be sure that you escape your greater than and less than signs in the comments. If you don’t, the internets will interpret them as html and your super-great formula will be lost forever.
Kim E:
Hi!
Not sure it is a BETTER way… but is does not involve any knowledge of ASCII code numbers..
here it is:
=MATCH(FALSE,EXACT(MID(E12,ROW($A$1:$A$255),1),LOWER(MID(E12,ROW($A$1:$A$255),1))),0)
It uses the fact that of all ASCII characters only uppercases get changed by the LOWER function
Creating (by macro or by name) a IsUpperCase function makes more sense to me though…
Cheers
K
21 February 2007, 7:00 pmMartin:
How about this formula? Not better as such but it seems to work.
21 February 2007, 7:43 pm{=SMALL(IF(EXACT(PROPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),1)}fzz:
Don’t parse the string twice. Once will do.
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)
21 February 2007, 9:12 pmfzz:
$#@! blog software trimmed the rest of my message.
Or download and install Laurent Longre’s MOREFUNC.XLL add-in and use its regular expression functions, e.g.,
=REGEX.FIND(A1,”[A-Z]”)
And consider finding, say, the 3rd upper case letter.
=REGEX.LEN(A9,”([^A-Z]*[A-Z]){3}”)
Use the right tool for the task. In this case, it ain’t built into Excel.
21 February 2007, 9:14 pmfzz:
And it looks like I need to resubmit my first formula. OOps.
=MATCH(TRUE,ABS(CODE(MID($A$1,ROW($1:$255),1))-77.5)<13,0)
21 February 2007, 9:17 pmfzz:
And it looks like I need to redo my first formula.
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)<13,0)
22 February 2007, 1:28 amHarald Staff:
Stripping, converting and cleaning data like that is most often a run-once operation, no need to redo it every time Excel calculates. So I write VBA routines to do these things. (Yes I know it’s neither a fun solution nor a fun response …)
22 February 2007, 2:52 amkeepITcool:
Another one. Elegant, probably less efficient
22 February 2007, 4:19 amIn xl12+
{=MIN(IFERROR(FIND(CHAR(ROW(INDIRECT(”65:90″))),C1),”"))}
Before:
{=MIN(IF(ISERROR(FIND(CHAR(ROW(INDIRECT(”65:90″))),C1)),”",FIND(CHAR(ROW(INDIRECT(”65:90″))),C1)))}
Tushar Mehta:
“Regular Expressions” is the way to go. In addition to Laurent’s Morefunc approach, see
http://www.tmehta.com/regexp/
and for Excel UDFs
http://www.tmehta.com/regexp/add_code.htm
or just search the web.
22 February 2007, 6:31 amfzz:
One more time to try to get my first formula right.
[VB]=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)
22 February 2007, 11:11 amfzz:
@#$%!
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)<13,0)
22 February 2007, 11:13 amJerry Gluck:
Perhaps I’m naive, but couldn’t you create a UDF that would use the MID function & loop through the string with a counter that would return the position when the ASCI code fell in the range for upper case; something like CODE(MID(cellref, counter,1)) with an IF condition applied.
22 February 2007, 11:20 amRichard Schollar:
Another option which is very similar to KeepITCool’s (just avoids the use of error checking):
=MIN(FIND(CHAR(ROW(INDIRECT(”65:90″))),A1&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”))
Confirmed with Ctrl+Shift+Enter (of course).
22 February 2007, 12:54 pmDavid Hager:
Or even:
=MATCH(TRUE,ISERR(FIND(MID(s,ROW(1:255),1),LOWER(s))),0)
22 February 2007, 1:41 pmMItch Wheat:
Not sure if a user defined function was an allowable solution, but I think you’d be hard pushed to beat this for speed:
‘ Returns -1 if not found
Function FindFirstCapital(ByVal strInp As String) As Integer
Dim tmp As String
Dim i As Integer
Dim pos As Integer
tmp = LCase$(strInp)
pos = -1
For i = 1 To Len(tmp)
If Mid$(tmp, i, 1) Mid$(strInp, i, 1) Then
pos = i
Exit For
End If
Next
FindFirstCapital = pos
22 February 2007, 7:18 pmEnd Function
Michael:
Dick -
Maybe a simple UDF?
For FirstCap = 1 To Len(Cell.Value)
If Mid(Cell.Value, FirstCap, 1) Like "[A-Z]" Then Exit For
Next FirstCap
End Function
And =IF(firstcap(A1)>LEN(A1),NA(),firstcap(A1))
...Michael
23 February 2007, 7:21 amDoug Jenkins:
Well if it's speed we're after, try:
' Returns -1 if not found
' Enter as an array function
Dim NumCells As Long, i As Long, j As Long, PositionA() As Long
CapText = CapText.Value
NumCells = UBound(CapText) - LBound(CapText) + 1
ReDim PositionA(1 To NumCells, 1 To 1)
For i = 1 To NumCells
For j = 1 To Len(CapText(i, 1))
If Mid(CapText(i, 1), j, 1) Like "[A-Z]" Then Exit For
Next j
If j> Len(CapText(i, 1)) Then j = -1
PositionA(i, 1) = j
Next i
FirstCapA = PositionA
End Function
On my machine with a column of over 65,000 shortish strings this recalculates in less than 1 second, compared with over 100 seconds for the non-array functions.
23 February 2007, 6:35 pmDoug Jenkins:
To be fair to the non-array functions, their speed increases dramatically if you close and re-open the file after entering the function in the VBA editor, but the array function is still much faster.
23 February 2007, 6:44 pmIoannis Varlamis:
The formulas above can work only for the English alphabet.
This formula works with any alphabet that has upper and lower letters.
For the Greek alphabet it works very well.
{=MATCH(0,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),0)}
24 February 2007, 3:41 pmCharles Williams:
Fastest method seems to be a UDF using a Byte array. This function takes 16 millisecs for 2000 strings and 8 millisecs when coded as an array formula.
Dim aByte() As Byte
Dim j As Long
FirstCap = -1
aByte = theRange.Value2
For j = 0 To UBound(aByte, 1) Step 2
If aByte(j) < 91 Then
If aByte(j) > 64 Then
FirstCap = (j + 2) / 2
Exit For
End If
End If
Next j
End Function
Overall the VBA UDFs and MOREFUNC are significantly faster (8 to 56 millisecs versus 320-380 millisecs) than the array formulae.
Tests were done on 2000 character strings length 26 with a single randomly selected upper case character. All timings in milliseconds using Range.calculate in manual calculate mode.
Timings in Millisecs:
2 March 2007, 12:02 pmFirstcap (Byte array) 16
AFirstcap (array function version of byte array) 8
If Mid(Cell.Value, FirstCap2, 1) Like "[A-Z]" 56
If Mid$(tmp, i, 1) Mid$(strInp, i, 1) Then 41
=MATCH(TRUE,ISERR(FIND(MID(A1,ROW($1:$255),1),LOWER(A1))),0) 323
Doug's array function 17
=MATCH(TRUE,ABS(CODE(MID(A1,ROW($1:$255),1))-77.5)
rakhmat:
can somebody help me, how to show/parse only the consonant character in excel
for example :
Asia Global Media ------> SGLBLMD
please email me the formula,
many thanks
Rakhmat
21 May 2007, 5:47 am