proper-type function when II, IV on end of name

dgr7

Board Regular
Joined
Apr 5, 2006
Messages
225
hello,
I've in the past used the Proper() function to convert all cap names like
JOHN SMITH JR
to
John Smith Jr
but I've ran into a problem in that the Proper() function also changes
BOB SMITH IV
into
Bob Smith Iv.

What function or VBA code could I use that when the name has IV or II or III after it to leave those letters in CAPS and change the rest of the name to Proper?
thanks in advance,
david
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jakeb3482

Board Regular
Joined
Mar 6, 2006
Messages
74
you could maybe make an IF statement that says if string equals IV or III or II then keep those capital...
 

dgr7

Board Regular
Joined
Apr 5, 2006
Messages
225
could you help me with what the IF statement syntax...
My thoughts on this is I think it'd have to do something like

IF
right two characters of string are II or III or IV
then
Concatenate(Proper(the characters up to the II, etc.) and a space and the right two characters)
else
Proper the whole string

how would I write that where Excel could use it?
 

jakeb3482

Board Regular
Joined
Mar 6, 2006
Messages
74
i gotta finish some stuff up right now. ill try to help you during my lunch break unless someone gets to it before me.
 

dgr7

Board Regular
Joined
Apr 5, 2006
Messages
225

ADVERTISEMENT

Can anyone help me with this??

thanks.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try eg:

=IF(RIGHT(A1,3)="III",PROPER(SUBSTITUTE(A1,"III",""))&"III",IF(OR(RIGHT(A1,2)="II",RIGHT(A1,2)="IV"),PROPER(LEFT(A1,LEN(A1)-2))&RIGHT(A1,2)))
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Are you stipulating a limit on how large the numeric suffix can be? For names up to the fifth generation (John Jones V) you can test the last two characters (although II and III) presents a problem. If you are getting up to larger values (Emperor Maximillian XXVIII) you have to have more conditionals...

Thinking out loud, I would test the last character - if it is anything other than I or V (or maybe X - see above), you aren't dealing with a numeric suffix - the function should just run the string through PROPER() and return the result. If you DO get one of those letters, you have to test against a name like Charles Simonyi by examining the second-last character - again, if it is NOT I, V, X or "space" (I can't think of any Ls, Cs or especially Ds or Ms!) then you are dealing with a regular name - apply the PROPER() function.

If you are dealing with a numeric suffix, you have to test to get the whole suffix - if the second-last character was a "space" then you know that the suffix is I (probably only applicable to royalty), V or X. Apply UPPER to the left-part of the string to LEN()-1, and append the numeric suffix (probabaly after applying UPPER().

If the second-last character is not a "space" then you have to work your way back, character by character to find the space. There are quick formulas for finding the last space in a string - you could either use those or just go step-by-step. I prefer the step by step method, although it is probably slower, since it allows you to test for unexpected values, like -say- F, which would indicate a different error, like a missing space or something. Once you find your numeric suffix (actually, the location of the space that seperates it from the name) you would apply PROPER() to the left-part to that seperator and UPPER to the right-part from the seperator, and then append them.

If you need a hand with the code, post a note - otherwise, good luck!
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
I was curious, so I did it anyway...
Code:
Function PropNum(stInput As String) As String
'returns stInput in Proper Case, with Roman Numeral suffixes
'(as in John Smith IV) in Upper Case.
'Possible errors flagged by appending "**" as an error code

Dim stData     As String
Dim i          As Integer
Dim stError    As String

stData = Trim(stInput)
If iVal(stData, 0) = 2 Then
    PropNum = WorksheetFunction.Proper(stData)
Else

    For i = 1 To Len(stData)
        Select Case iVal(stData, i)
            Case 0
                Exit For
            Case 2
                stError = "**"
                Exit For
            Case 1
        End Select
    Next i

PropNum = WorksheetFunction.Proper(Left(stData, Len(stData) - i))
PropNum = PropNum & UCase(Right(stData, i))
PropNum = PropNum & stError

End If
End Function

Function iVal(stData As String, iChar As Integer) As Integer
' returns value to indicate whether a selected character
' in a string could be part of a Roman Numeral suffix,
' ie John Jones IV
' return values:
' if possible Rom Numeral: 1
'                   space: 0
'       not a Rom Numeral: 2

Select Case UCase(Mid(stData, Len(stData) - iChar, 1))
    Case "I", "V", "X"
        iVal = 1
    Case " "
        iVal = 0
    Case Else
        iVal = 2
End Select

End Function
Edited to start the inspection with the last character, rather than second-last
This will return Proper Case names and UPPER CASE Roman Numeral suffixes for any length of RN suffix (although I didn't include L, D or M as possible RN Components. It will append an error code of ** to the value if it encounters an unusual condition - typically a missing space, so:
"JOHN JONES IV" returns John Jones IV, while
"JOHN JONESIV" returns John JonesIV** and
"JOHN JONES,IV" returns John Jones,IV**
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
If you use PROPER to convert addresses, you will have a whole lot of "exceptions" where the conversion is not what you desire. I have identified over 50 of them and fix them through a series of substitutions.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
dcardno:
That has problems if the last name was Olive for example.

Perhaps:
Code:
Function PropNum(stInput As String) As String

Dim stData
Dim i As Integer
Dim j As Integer
Dim u As Integer

stData = Split(stInput)
i = ARABIC(stData(UBound(stData)))
u = UBound(stData)
For j = 0 To u
    stData(j) = Application.WorksheetFunction.Proper(stData(j))
Next j

If IsNumeric(i) Then
    If i > 0 Then
        If UCase(stData(u)) = Application.WorksheetFunction.roman(i) Then _
            stData(u) = UCase(stData(u))
    End If
End If

PropNum = Join(stData)
End Function

Code:
Private Function ARABIC(ByVal r As String) As Variant
Dim i As Integer
Dim ch As String
Dim result As Long
Dim new_value As Long
Dim old_value As Long

r = UCase$(r)
old_value = 1000

For i = 1 To Len(r)
    ch = Mid$(r, i, 1)
    Select Case ch
        Case "I"
            new_value = 1
        Case "V"
            new_value = 5
        Case "X"
            new_value = 10
        Case "L"
            new_value = 50
        Case "C"
            new_value = 100
        Case "D"
            new_value = 500
        Case "M"
            new_value = 1000
        Case Else
            ARABIC = -1
            Exit Function
    End Select

    If new_value > old_value Then
        result = result + new_value - 2 * old_value
    Else
        result = result + new_value
    End If

    old_value = new_value
Next i
ARABIC = result
End Function
Book4
CDEF
8james fox ivJames Fox IV
9joe oliveJoe Olive
10joe green xxJoe Green XX
11fred james mcmFred James MCM
12fred vimFred Vim
13fred jones viFred Jones VI
14scott xilScott Xil
Sheet1
 

Forum statistics

Threads
1,136,994
Messages
5,679,030
Members
419,800
Latest member
spvsr999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top