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 thespace 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.
Excel is great at splitting text across multiple columns. For basic text separation (e.g. putting first and last name in separate columns) the LEFT and RIGHT functions work fine. But these functions fall short when applied to data that follow an inconsistent pattern. In the below example, the errant inclusion of a middle name wrecks havoc with our formula results.
Using the SUBSTITUTE function one can identify and replace the last space in the cell with a uniquely identifying character (e.g., an underscore). This character can then be used as a delimiter to separate Lastname from full name.
SUBSTITUTE: replace character(s) within a cell with specified character(s)
= SUBSTITUTE ( text, old text, new text, instance)
Note, instance is optional; when omitted, every occurrence of old text is replaced with the new.
Linking the worksheet name to a cell in the spreadsheet is easily accomplished using Excel’s CELL function. Once joined by the MID and SEARCH functions, you need only change the sheet’s name and the linked cell will update to match.
Displaying the Sheet Name in a Cell
Type (or copy and paste) the following formula into a cell