	A	B	C	D	E
1	Works on Both (see formulas at end of tablet
2	Ron de Bruin	de Bruin	de Bruin	de Bruin	de Bruin
3	R. Roger Wilson Jones	Jones	Jones	Jones	Jones
4	Jan van Ness	van Ness	van Ness	van Ness	van Ness
5
6	Just Testing
7	Louis de Bourbon, XVI	XVI	de Bourbon	de Bourbon	de Bourbon
8	Dr. Seymoure J. Brown, D.D.S	S	Brown	Brown	Brown
9	John Inverness	Inverness	John	Inverness	Inverness
10	John Mackey	Mackey	Mackey	Mackey	Mackey
11	roy rogers	#VALUE!	#VALUE!	#VALUE!	#VALUE!
12	John Xavior	Xavior	John	Xavior	Xavior
13
14	Fails on Inititial Simpler Formula, none can handlel capitalized De
15	Lee De Forest	#VALUE!	Forest	Forest	Forest
16
17	Both Initial Formulas Fail , later formulas okay
18	G. Espina-Lopez	Lopez	Lopez	Espina-Lopez	Espina-Lopez
19	Dag Hammarskjöld	#VALUE!	#VALUE!	Hammarskjöld	Hammarskjöld
20	John O'Connor	Connor	Connor	O'Connor	O'Connor
21
22	Fails on Fancier Initial Formula, later formulas okay
23	Ronald MacDonald	MacDonald	#VALUE!	MacDonald	MacDonald
24	David McRitchie	McRitchie	#VALUE!	McRitchie	McRitchie
25
26	Formulas, MOST IMPROVED ON THE RIGHT

C2: =IF(TRIM(A2)="","",personal.xls!resubstr(A2,"\b([a-z]+ +)*[A-Z][a-z]*(?=(( +)(Sr\.?|Jr\.?|[IVX][IVX]*))|,|\s*$)"))

B2: =IF(TRIM(A2)="","",personal.xls!resubstr(A2,"\b([a-z]+ +)*[A-Z]\w*$") )

D2: =IF(A2="","",personal.xls!resubstr(A2,"\b([a-z]+\s+)*[A-Z](\w+\S?)*([-'][A-Z](\w+\S?)*)?\b(?=(\s+([JS]r\.?|[IVX]+))?\s*$|,)") )

E2: =IF(A2="","",personal.xls!resubstr(A2,"\b([a-zsoz¡ßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ]+\s+)*"&"[A-ZSOZYÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞ](\w+\S?)*"&"([-'][A-ZSOZYÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞ](\w+\S?)*)?"&"\b(?=(\s+([JS]r\.?|[IVX]+))?\s*$|,)"))

				
Function resubstr(s As String, p As String, Optional n As Long = 0) As String				
    Dim re As Object, m As Object				
    Set re = CreateObject("VBScript.RegExp")				
    re.Pattern = p				
    re.Global = True				
    Set m = re.Execute(s)				
    resubstr = IIf(m.Count > 0, m(n).Value, "")				
End Function				
 
s  -- original string				
p -- parameters

Please send your comments concerning this web page to: David McRitchie send email comments