Excel PROPER Exclusions

I would like the ability to exclude or ignore certain suffixes or phrases from the PROPER function.  For example, if I apply this function to WILSON III, it will be converted to Wilson Iii.  Obviously, in this situation, I would like to exclude the suffix.  Is this possible?
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
You'll need code for this.  This function will cover you with the suffixes for III - X:

Function BetterProper(InputString As String) As String
    Dim arr As Variant
    Dim Counter As Long
    arr = Split(InputString, " ")
    For Counter = LBound(arr) To UBound(arr)
        If Counter <> UBound(arr) Then
            arr(Counter) = Application.Proper(arr(Counter))
            Select Case arr(Counter)
                Case "III", "IV", "V", "VI", "VII", "VIII", "IX", "X"
                    ' do nothing
                Case Else
                    arr(Counter) = Application.Proper(arr(Counter))
            End Select
        End If
    BetterProper = Join(arr, " ")
End Function

Open in new window

Michael FowlerSolutions ConsultantCommented:
The only way I could see to do this would be create a list of exceptions and then use a formula to look up if the exception applies. In reality this would be almost impossible to implement as the number exceptions would be huge. Personally I think it would be easier to correct these manually or ignore them.
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for Patrick Matthews's comment #a40451145
Assisted answer: 250 points for Michael74's comment #a40448964

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.