Use Advanced Search Wildcards to Format Names: Last, First Middle

lastnames-first-gif

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.

  1. Select your list of names.
  2. Press CTRL+H. The REPLACE dialog appears.
  3. If necessary click More button to display full dialog.
  4. Check Use Wildcards.
  5. In the Find what field type: (*) ([! ]@)^13
    Important: observe spaces.
  6. In the Replace with field type: \2, \1^p
    Important:
    note the space after the comma.
  7. Click Replace All. When prompted to “..continue searching the remainder of the document” click No.
  8. 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).

  • \2,_\1^p 

Note:  _ is used above to illustrate use of space.

Codes that work with Find and Replace

To find Type
Paragraph mark ( 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!

Cheers!

hɔuᴉnb

Related:

Excel Text 4 of 4: Using SUBSTITUTE to Parse Text

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.

Text4Substitute(2)

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.

ScreenShot-2015-Jun-03-126-AM

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.

Examples:

  • =SUBSTITUTE(“Mad Max”, a,i) returns Mid Mix
  • =SUBSTITUTE(“mississippi, i,“”) returns msssspp
  • =SUBSTITUTE(“banana”, a,Q”,2) returns banQna
LEN Counts the number of characters =LEN(antidisestablishmentarianism) returns 28

Click here for a 5 minute tutorial:

Cheers!

hɔuᴉnb

Additional reading:

%d bloggers like this: