ABCDE
1 Works on Both (see formulas at end of tablet
2Ron de Bruinde Bruin de Bruinde Bruinde Bruin
3R. Roger Wilson JonesJones JonesJonesJones
4Jan van Nessvan Ness van Nessvan Nessvan Ness
5      
6 Just Testing
7Louis de Bourbon, XVI XVIde Bourbon de Bourbonde Bourbon
8Dr. Seymoure J. Brown, D.D.S SBrownBrown Brown
9John InvernessInverness JohnInverness Inverness
10John MackeyMackey MackeyMackeyMackey
11roy rogers #VALUE! #VALUE! #VALUE! #VALUE!
12John Xavior Xavior JohnXavior Xavior
13      
14 Fails on Inititial Simpler Formula, none can handlel capitalized De
15Lee De Forest #VALUE!Forest ForestForest
16      
17 Both Initial Formulas Fail , later formulas okay
18G. Espina-Lopez Lopez LopezEspina-Lopez Espina-Lopez
19Dag Hammarskjöld #VALUE! #VALUE!Hammarskjöld Hammarskjöld
20John O'Connor Connor ConnorO'Connor O'Connor
21      
22 Fails on Fancier Initial Formula, later formulas okay
23Ronald MacDonaldMacDonald #VALUE!MacDonald MacDonald
24David McRitchieMcRitchie #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


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved