Solved

# Excel PROPER Exclusions

Posted on 2014-11-17
131 Views
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?
0
Question by:amergts
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Expert Comment

ID: 40448964
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.
0

LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 40451145
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))
Else
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
Next

BetterProper = Join(arr, " ")

End Function
``````
0

LVL 47

Expert Comment

ID: 40494430
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦