PDA

View Full Version : Solved: Problem with Proper Case



Opv
07-01-2013, 04:49 PM
Could someone please advise as to why converting strings that include a possessive noun, i.e., "the person's opinion," to Proper Case results "The Person'S Opinion"? Is there a work-around?

Kenneth Hobs
07-01-2013, 05:54 PM
Since there is no Proper function in VBA, I guess you mean the function similar to this one? IN that case, modify the line that I did.
Sub Test_Proper()
Dim s As String
s = "the person's opinion,"
MsgBox Proper(s)
End Sub

' http://support.microsoft.com/kb/815282
Function Proper(X)
'Capitalize first letter of every word in a field.
Dim Temp$, C$, OldC$, i As Integer
If IsNull(X) Then
Exit Function

Else
Temp$ = CStr(LCase(X))
' Initialize OldC$ to a single space because first
' letter must be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
C$ = Mid$(Temp$, i, 1)
' Single quote exception added by Ken.
If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") And OldC$ <> "'" Then
Mid$(Temp$, i, 1) = UCase$(C$)
End If
OldC$ = C$
Next i
Proper = Temp$
End If
End Function

Opv
07-01-2013, 06:09 PM
Since there is no Proper function if VBA, I guess you mean the function similar to this one? IN that case, modify the line that I did.


Thanks. My original script worked using the following:


Range("A1").Value = Application.Proper(Range("A1").Value)


That worked until I happened upon a string that included an apostrophe. Regardless, your script solves the problem.

Thanks!