Solved: Inserting symbols after the last string/word.

[defcon_3]

Hi guys, I want to put a separator after each last word. I can do it in excel but i have difficulties converting it to word macro. See sample below.

Original Format

John Doe
Jane Fleming
Roxel Allen Berg
Max Shiba Daemon
Grant Morris Kalk

Should be result.

John*Doe
Jane*Fleming
Roxel Allen*Berg
Max Shiba*Daemon
Grant Morris*Kalk

Removing the space and substituting it to "*"

Thanks.

[Talis]

Find: (<[A-z]@>^13)
Repl: *\1

Check 'Use wildcards'.
The first character of the Find string is a space: <space>(<[A-z]@>^13)

[defcon_3]

Thanks so much for the solution.
SOLVED

[Talis]

If you are likely to encounter hyphenated surnames (eg Armstrong-Jones) then add a hyphen/minus sign in the Find string:

Find: (<[A-z-]@>^13)
Repl: *\1

Because of its position it doesn't need to be escaped, Word figures out how you want to use it, but you could escape it if you think it makes it more understandable:


Find: (<[A-z\-]@>^13)

[Talis]

Now I've realized that there are other names with non-alphabetic components sych as O'Byrne, O'Brien, O'Keefe.
So thought this would work better:


Find: ([! ]@^13)
Repl: *\1

The Find string says find <space> plus anything but a <space> plus a carriage return.
As previously, check 'Use wildcards' and note space at front of search string.
Here's something to test it on:


John White
Patrick Flanagan
Fred Smith-Jones
Jim O’Keefe
Peter Jim O’Brien
Gary Greg Olsen

After 'Gary Greg Olsen' you may need to add a Carriage Return.

[defcon_3]

Very good thats great. I didnt noticed that. Thanks a lot again :)

[macropod]

Now all you need to figure out is how to handle surnames consisting of two or more words with spaces in between (eg lookup 'Van' in any phone book) ...

[Talis]

Your wish is my command -
Sub AddAsterix()
Dim rngNames As Range
Dim preName As Variant
Dim sufName As Variant
Dim i As Long
Set rngNames = Selection.Range
If Len(rngNames) < 1 Then
MsgBox "Select the text first!", vbOKOnly, "No text selected"
Exit Sub
End If
preName = Array("ben", "da", "Da", "Dal", "de", "De", "del", "Del", "den", _
"der", "Di", "du", "e", "la", "La", "le", "Le", "Mc", "San", "St", "Ste", "van", _
"Van", "Vander", "vel", "von", "Von", "y")
sufName = Array("Jr", "Jnr", "jr", "jnr", "Sr", "Snr", "sr", "snr", "2", "II", "III", "IV", _
"Jr.", "jr.", "Jnr.", "jnr.", "Sr.", "Snr.", "sr.", "snr.", "2.", "II.", "III.", "IV.")

With rngNames
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Forward = True
.Wrap = wdFindStop
.MatchWholeWord = True
.MatchWildcards = True
.MatchSoundsLike = False
.MatchAllWordForms = False
.Format = False
.MatchCase = True
For i = LBound(preName) To UBound(preName)
.Text = "([ _])" & preName(i) & "([ _])"
.Replacement.Text = "\1" & preName(i) & Chr(95)
.Execute Replace:=wdReplaceAll
Next i
End With
End With
With rngNames
With .Find
For i = LBound(sufName) To UBound(sufName)
.Text = Chr(32) & sufName(i)
.Replacement.Text = Chr(95) & sufName(i)
.Execute Replace:=wdReplaceAll
Next i
End With
End With
With rngNames
With .Find
.MatchWildcards = True
.Text = " ([! ]@^13)"
.Replacement.Text = "*\1"
.Execute Replace:=wdReplaceAll
End With
End With
With rngNames
With .Find
.MatchWildcards = False
.Text = Chr(95)
.Replacement.Text = Chr(32)
.Execute Replace:=wdReplaceAll
End With
End With
End Sub

Test file:


John Doe
John Doe, Jr.
John Doe II
John A. Doe
John A. Doe, Jr.
John A. Doe 2
John A. Kenneth Doe
John A. Kenneth Doe, Jr.
John A. Kenneth Doe III
Juan de la Vega
Juan e Vega, Jr.
Juan von Vega Snr
Juan Velasquez y Garcia
Juan Velasquez den Garcia, Jr.
Juan Velasquez vel Garcia snr
Juan Q. Mc Vega
Juan Q. la de Vega, Jr.
Juan Q. de la Vega III
Juan Q. Velasquez y Garcia Snr.
Juan Q. Velasquez ben Garcia, Jr.
Juan Q. Velasquez y Garcia IV
Juan Q. Xavier de la Vega
Juan Q. Xavier le Vega, jr
Juan Q. Xavier de la Vega III
Juan Q. Xavier Velasquez y Garcia
Juan Q. Xavier Velasquez du Garcia, Jr.
Juan Q. Xavier Velasquez y Garcia Sr.
Van Damme
Geronimo

As the search looks for a preceding space, 'Geronimo' doesn't get a star!

[macropod]

Hi Talis,

Well done. Except for some Hebrew names prefixed with 'Ben' instead of 'ben' (I understand why 'Ben' surname prefixes would be difficult to differentiate from 'Ben' as a given name), it's looking pretty good. Easily added to for further variants than might be encountered, too.

[fumei]

Agreed. Very well done.