Reformatting a list of names is challenging when some of the names have middle names and others do not. Using Word’s Advanced Replace dialog and wildcards makes it easy to rewrite a list in Lastname, First M. format.
3 minute e-Learning demo:
To Reformat a List of Names to Last, First M.
- Select your list of names.
- Press CTRL+H. The REPLACE dialog appears.
- If necessary click More button to display full dialog.
- Check Use Wildcards.
- In the Find what field type: (*) ([! ]@)^13
Important: observe spaces.
- In the Replace with field type: \2, \1^p
Important: note the space after the comma.
- Click Replace All. When prompted to “..continue searching the remainder of the document” click No.
- Close the dialog.
Why it works
The find pattern looks for two groups: first name with optional middle name or initial and last name.
- (*)_ finds expression1, first and middles names
- ([!_]@)^13 finds expression2, the remainder of name up to and including hard return (ascii char 13)
These names can now be referred to as expressions \1 and \2. In our replace statement we transpose them, separate them with a comma and space, and end with a hard return (^p).
Note: _ is used above to illustrate use of space.
Codes that work with Find and Replace
|Paragraph mark ( )||^p (doesn’t work in the Find what box when the Use wildcards option is turned on), or ^13|
|One or more occurrences of the previous character or expression||@
ex: lo@t finds lot and loot.
|Any single character except the characters in the range inside the brackets||[!_]
ex: t[!a-g]ck finds tock and tuck, but not tack or tick.
Comments and questions are always welcome!
- Using Wildcards with Word Find and Replace features
- Excel Text 4 of 4: Using SUBSTITUTE to Parse Text
- Find and replace text and other data in a Word document