Yes, it does Myrna, which David's post lead me to understand; my thanks to
the both of you.

This would have a negative impact on IBM and/or McRitchie, which may be
unavoidable outside of setting up an array of key terms given the all-cap
starting point.

And, I chose not to deal with proper nouns or acronyms, in that sense, the
function is (very?) incomplete.

I did a little testing, David. <g

I just tested your function, and it failed (#VALUE!) on the very first cell which
was empty. I know the poster said he started with all caps,
and your encompassing SUB would eliminate that possibility.

Quite right, a slight oversight on my part! And a quick fix:

'-------------------
Function sCase(ByVal strIn As String) As String
Dim bArr() As Byte, I As Long, i2 As Long
If strIn = vbNullString Then Exit Function
Let bArr = StrConv(LCase$(strIn), vbFromUnicode)
Select Case bArr(0)
Case 97 To 122
bArr(0) = bArr(0) - 32
End Select
For I = 1 To UBound(bArr)
Select Case bArr(I)
Case 105
If Not I = UBound(bArr) Then
Select Case bArr(I + 1)
Case 33, 39, 44, 46, 58, 59, 63, 148, 160
bArr(I) = bArr(I) - 32
Case 32
If bArr(I - 1) = 32 Then _
bArr(I) = bArr(I) - 32
End Select
ElseIf bArr(I - 1) = 32 Then _
bArr(I) = bArr(I) - 32
End If
Case 33, 46, 58, 63
For i2 = I + 1 To UBound(bArr)
Select Case bArr(i2)
Case 97 To 122
bArr(i2) = bArr(i2) - 32
I = i2: Exit For
End Select
If bArr(i2) < 32 And bArr(i2) < 33 And bArr(i2) < 46 _
And bArr(i2) < 63 Then
I = i2: Exit For
End If
Next
End Select
Next
sCase = StrConv(bArr, vbUnicode)
End Function
'-------------------

Thanks.

The range I hard-coded was the specified range by the OP, and you're
correct, one could very easily make this dynamic without too much effort.

You could convert SCase() to an inline sub or crank out an array as Peter T
has done, or disable recalculations temporarily. The algorithm itself is the
most time intensive consideration I suspect.

Thanks for the feedback Peter, glad to hear you like it. Byte Arrays are
pretty efficient. And I didn't attempt to optimize that sub procedure, but
indeed, looping through an Array should be much faster than looping through a
Range if they're sized equally.

The speed of using a Byte Array caught my attention one day I when I decided
I was going to crop an MP3 file with Excel. So I wrote a procedure using
Binary File Access and a couple of Byte Arrays:

http://mrexcel.com/board2/viewtopic.php?p=306631#306631

(I'm not sure that particular code is optimized...) In any case, I thought I
was going to be sitting around all day, but not at all. I didn't even need a
timer to see how fast these things are. I just did it again on a 7.5 MB file,
while the procedure loops 7.5 million times (used Currency variables), I cut
the file in half in 16 seconds. This strikes me as fast.

I'm not necessarily saying you should use RegExp or Byte arrays over one
another, in fact I've seen string parsing on very large strings performed
more efficiently with RegExp that I could match with the Byte Array. It's
hard to get around some of the overhead with StrConv() and that VBA is a
high-level and slower language.

I just thought I'd write a UDF for Teresa that I knew would be pretty
quick-like and fairly robust.

Cheers,
Nate Oliver