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

[dgr7]

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

 

[dcardno]

dcardno:
That has problems if the last name was Olive for example.

I'm not sure why it would have problems with "JOHN OLIVE" - when I tried it it returned "John Olive." Where it DID have problems was one of my described test cases, that of "CHARLES SIMONYI" which returns "Charles SimonyI**" - the capital "I" is due to the routine interpreting the terminal "i" as a Roman "one" and so passing it through UPPER() and then appending the two asterisks to indicate a supposed error of a missing space between the (presumed) last name of "Simony" and the "I."

I had completely forgotten about the "Split" function, which makes things much easier...

 

[Scott Huish]

You're revised code returns John Olive correctly, but if they didn't have an e on the end of the name it returns Joe OlIV**

this is why, rather than just see if there are V or X or I or whatever, I ran code to try and convert the last value to a number and then it checks to see if is the "classic" Roman numeral form of that number to determine a match.

While there is still possibility for errors, if there last name was Xi for example, which is a valid Roman numeral (XI), I think the chances are greatly reduced.

 

[jindon]

Does this help?

Function ProperName(txt As String) As String
Dim m As Object
With CreateObject("VBScript.RegExp")
    .Pattern = "\S+"
    .Global = True
    For Each m In .execute(txt)
        ProperName = Trim(ProperName & WorksheetFunction.Proper(m.Value) & Chr(32))
    Next
    .Pattern = "\b[ivxmcld]+$"
    .IgnoreCase = True
    ProperName = ProperName & UCase(.execute(txt)(0))
End With
End Function

 

[Peter_SSs]

I wonder if a formula approach is still of any interest. I think this is a way to cope with pretty much any Roman numerals at the end, though the formula is a fairly long one.

1. Make a Roman Numeral table as follows. From E2 down enter the numbers 1, 2, 3 etc, as far as you want to go. In D2 (copied down): =ROMAN(E2)
2. B1 (copied down):
=IF(ISNA(MATCH(REPLACE(A1,1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),""),D:D,0)),PROPER(A1),PROPER(LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))&REPLACE(A1,1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),""))

Mr Excel.xls
	A	B	C	D	E	F
1	JOHN Smith III	John Smith III		Roman	Arabic
2	John SMITH XII	John Smith XII		I	1
3	John SMITH	John Smith		II	2
4	HENRY VIII	Henry VIII		III	3
5	JOHN SMITH JR	John Smith Jr		IV	4
6	LOUIS XIV	Louis XIV		V	5
7				VI	6
8				VII	7
9				VIII	8
10				IX	9
11				X	10
12				XI	11
13				XII	12
14				XIII	13
15				XIV	14
16				XV	15
17				XVI	16
18				XVII	17
19				XVIII	18
20
Proper

 

[Darren Bartrup]

Using minimum amount of VBA you could try:

Create a function in a module:

Public Function Reverse(TextString As String)

    Reverse = StrReverse(TextString)

End Function

If BOB SMITH IV is in cell A1, use this:
=PROPER(reverse(RIGHT(reverse(A1),LEN(reverse(A1))-SEARCH(" ",reverse(A1))))) & " " & reverse(LEFT(reverse(A1),SEARCH(" ",reverse(A1))-1))

Or split down into component parts:

• Cell B1: =reverse(A1) - returns VI HTIMS BOB (in cell B1)
  Cell B2: =SEARCH(" ",B1) - returns 3 (the first space in B1)
  Cell B3: =LEFT(B1,B2-1) - returns VI
  Cell B4: =RIGHT(B1,LEN(B1)-B2) - returns HTIMS BOB
  Cell B5: =PROPER(reverse(B4)) - returns Bob Smith
  Cell B6: =reverse(B3) - returns IV
  Cell B7: =B5 & " " & B6 - returns Bob Smith IV

The one problem is that it leaves the last word of the name as it was entered - JOHN JUPILER will return as John JUPILER

 

[dcardno]

if there last name was Xi for example, which is a valid Roman numeral...

Yes - I see what you meant about "OLIV" as a last name (or Xi).

Until a few years ago the Registrar of Vital Statistics here in BC would not recognize numeric suffixes - you could register "Jr" but not II or III. One couple got around that by changing young John Smith's surname to Smith-Iv - which of course they rendered as "Smith IV"...