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:
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:
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
Those are the ASCII codes for mdtTxnDate.
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:
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:
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.
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
How about this formula? Not better as such but it seems to work.
Don’t parse the string twice. Once will do.
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)
$#@! 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.
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)
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)
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 …)
Another one. Elegant, probably less efficient :)
In 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)))}
“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.
One more time to try to get my first formula right.
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)@#$%!
=MATCH(TRUE,ABS(CODE(MID(s,ROW($1:$255),1))-77.5)<13,0)
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.
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).
Or even:
=MATCH(TRUE,ISERR(FIND(MID(s,ROW(1:255),1),LOWER(s))),0)
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
End Function
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
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.
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.
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)}
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:
Firstcap (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)
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
How could I go about modifying this so I capture any ALL CAPS text in a string ie
Nick MANNING — Result — MANNING
Jack THE RIPPER — Result — THE RIPPER
Ben HILL J OFFER — Result — HILL J OFFER
The last example might be more difficult but one for the first two would be great.
Many thanks
Nick
Dim i As Long
Dim sReturn As String
sReturn = Mid$(sInput, lStart, Len(sInput))
For i = Asc(“a”) To Asc(“z”)
sReturn = Replace$(sReturn, Chr$(i), “”)
Next i
ReturnCaps = Trim(sReturn)
End Function
Use as
=returncaps(A1,2)Excellent – Thank you very much. I will need to modify it a little as I have a few European names and it catches accented charaters and also the Capitalised letter from the second word of two worded first names- John Paul SMITH returns P SMITH.
You response is fantasic and better than anything I have been able to find in months of posting and searching. Thanks again.
@Nick,
Does this function do what you want?
Dim X As Long, Z As Long, Temp As String
For X = 1 To Len(S)
If LCase(Mid(S, X, 1)) = Mid(S, X, 1) Then Mid(S, X, 1) = ” “
Next
For X = 1 To Len(S) – 1
If Mid(S, X, 2) Like “[! ][! ]” Then
S = Mid(S, X)
For Z = Len(S) – 1 To 1 Step -1
If Mid(S, Z, 2) Like “[! ][! ]” Then
UpperOnly = Left(S, Z + 1)
Exit Function
End If
Next
End If
Next
End Function
umm can’t get it to work.. getting a #Name error
I am using it as
also tried
where A1 Contains the text Nick MANNING
What am i doing wrong?
@Nick,
The code was tested before I posted it, so I know it works. The only thing I can think of is you copied my code to the wrong location. I’m guessing you put it in either a sheet or workbook module instead placing it in a standard module (Insert/Module for the VB editor’s menu bar) where it needs to be. By the way, your first formula, as written, still won’t work because the argument is text and you did not put quote marks around it… the second formula should work fine as written.
@Rick
Not sure what was going on but tried it in a fresh document and worked like a treat. Thanks Heaps!
You are quite welcome… I’m glad you got it to work for you. I am pretty sure it does everything you mentioned you wanted in your earlier response to Dick, but if I missed anything, or if you want something additional, please feel free to ask. You can do that here at this forum or you can email me directly at rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbol they spell out).