Word: Transpose Surname, Firstname to Firstname Surname

I came across a heap of names styled ‘Surname,<space>Firstname’ (e.g. Smith, Jane) and needed to change them to ‘Firstname Surname’ (i.e. Jane Smith).

As with any find/replace operation, identifying the pattern is the first step. Once you’ve done that, the rest is pretty easy. In this example, the pattern was clear — each surname and first name started with a capital letter followed by one or more lower case letters, there was a comma after the surname, and then a space before the start of the first name. Each surname only had a single first name. Because names vary in length, I needed to use wildcards to specify matching the pattern for any number of letters.

Below is what I came up with for this swap — others more clever than me may have a more elegant way to do this, but this worked for me.

CAUTIONS and WARNINGS:

• I don’t advise doing a ‘replace all’ with this — if there’s anything else that matches the pattern that ISN’T a name, it will get changed too.
• This find/replace only finds whole names with a single capital letter (i.e. it finds Smith, Jones, Haythornthwaite, Jane, Rosemary, Jonathan). It does NOT find names with more than one capital (e.g. McDonald, AnnMarie) or with an apostrophe (e.g. O’Malley).
• Hyphenated words are found, but transpose incorrectly (e.g. Smith, Jane-Ann changes to Jane Smith-Ann not Jane-Ann Smith; similarly Jones-Brown, John changes to Jones-John Brown).
• Surnames with a first and middle name or initial will be found but transposed incorrectly (e.g. Smith, Jane K. Susan will become Jane Smith K. Susan instead of Jane K Susan Smith). Surnames with an initial letter instead of a first name will not be found (e.g. Philips, A. is not found)
• Names separated with anything other than a comma, or that have two or more spaces between the comma and the first name will NOT be found.
• Names with accents, umlauts, and other diacritical marks over letters (e.g. René) are found and transposed correctly.

Despite all the cautions and warnings above, if you have a long list of names to change, then you could run this find/replace, replacing one at a time and manually fixing the others that aren’t found or that will transpose incorrectly. It’s still quicker than doing them all manually.

Steps:

1. Save your document.
2. Press Ctrl+H to open the Find/Replace window.
3. Click the More button.
4. Select Use Wildcards.
5. In the Find What field, enter this (copy it from here and paste as there’s a space in the string of characters): (<[A-Z])([a-z]@>)(, )(<[A-Z])([a-z]@>)
6. In the Replace With field, enter this (again, copy/paste as there’s a space in here that’s hard to see): \4\5 \1\2
7. Click Find Next.
8. Click Replace if it finds a name you want to transpose; if not, click Find next to go to the next one. (Note: Replace All is super powerful and you could change things you don’t want to, so err on the side of caution and click Find Next > Replace > Find Next until all are done).

Explanation:

• Parentheses surround each ‘element’ of the find. These are represented by numbers in the replace (i.e. the 4th set of parentheses in the find becomes \4 in the replace)
• < indicates the beginning of a word; > indicates the end of a word
• [A-Z] looks for any upper case letter; [a-z] looks for any lower case letter
• @ looks for any number of the instruction immediately previous (e.g. [a-z]@> looks for any number of lower case letters up to the end of a word — this covers the varying length of names)

 

Word: Find duplicated words

This find/replace is based on Paul Beverley’s work, so full acknowledgement to him for teaching me how to do this via his YouTube videos and his free book.

********

Some of my authors inadvertently type the same word twice (e.g. is is, the the), and it’s often hard to pick these up when editing. If you run spellcheck, you may find them, but there’s no guarantee of that. The find and replace below uses wildcards to find any instance of duplicated words, followed by a space or a common punctuation mark, and then replaces that with a single word and the trailing space or punctuation.

NOTE: This find/replace only finds words with the exact same case, so it will find ‘the the’, ‘THE THE’, and ‘The The’, but it won’t find instances where each word has the same letters but with different cases (e.g. ‘the The’, ‘The the’, ‘tHe thE’ etc.)

Steps:

1. Press Ctrl+H to open the Find and Replace dialog box.
2. Click More, then select the Use wildcards option.
3. In the Find field, type: (<[A-Za-z]@)[ ,.;:]@\1>
   (Note: There’s a space in there, so I suggest you copy this Find string.)
4. In the Replace field, type: \1
5. Click Find Next then click Replace. Repeat.

 

How this works — at least how I *think* it works:

• Find: Look for the start of any word (<) made up of any number (@) of letters ([A-Za-z]) followed by a space or punctuation ([ ,.;:]) then repeat that find (@\1) until you can’t any more words that match the pattern (>).
• Replace: Replace the first element (the first of the duplicate words) with itself (that’s the \1 bit), which effectively deletes the rest.

[Links last checked December 2017]

Word: Find a year followed by a comma and replace with a semicolon

Another early morning question posed on Facebook…

The person was trying to use Word’s wildcard find and replace to convert all strings of Authorname nnnn, Authorname nnnn, Authorname nnnn, Authorname nnnn (i.e. any author’s name, followed by a 4-digit number for a year, such as Smith 2005, Jones 1997, etc., followed by a comma, followed by another author’s name etc.). He wanted to convert all the comma separators to semicolons, ending up with Authorname nnnn; Authorname nnnn; Authorname nnnn; Authorname nnnn. (I’ve italicised the text for clarity — it wasn’t in his original.)

Wildcard find/replace is all about finding the pattern and then figuring out how best to interpret that pattern in a meaningful way in how you search for what you want, and how you replace it with what you want.

In this example, an author name always ends in a lower case letter, is followed by a space, then four numbers for the year, a comma, a space, then an upper case letter for the next author’s name. The last item in the list doesn’t quite match the pattern (no comma, space, upper case letter following it),  but that one doesn’t need to change so we can ignore that variation to the pattern. He wanted to keep everything except the comma, which he wanted to change to a semicolon.

Here’s how I solved it using Word’s wildcard find and replace  (there may be a more elegant solution, but this one worked for me):

• Find: ([0-9]{4})(,)( )([A-Z]) 
• Replace: \1;\3\4

If you need to use this, I suggest you copy it as there’s a space in the third set of parentheses that you can’t see.

How this works:

• Find: Look for any number from 0 to 9 [0-9] that has 4 digits {4} — this is the first element and is surrounded by parentheses. Then look for a comma (another element, so also surrounded by parens). Next look for a space (wow, more parens), and finally look for any upper case letter [A-Z] and as it’s a unique element, surround it by parens too.
• Replace: Replace the first element (the 4-digit number) with itself (that’s the \1 bit), then a semicolon, then replace the third and fourth elements of the find with themselves (e.g. \3\4).

You keep everything you don’t want to change (elements 1, 3, and 4) and only change the second element by typing a semicolon in between elements 1 and 3.

 

 

Word: Wildcard replace with a backslash

This morning, well before I was properly awake, I solved a problem someone had posed on a Facebook group I’m in. They had an issue getting Word’s wildcard find and replace to do what they wanted and had asked members of the group to help. I’m writing this up for my own future reference as there’s some information in here about the peculiarities of the backslash character that I may need to use again in the future. [Random fact: The backslash character is known by several names, including the reverse virgule and the reverse solidus.]

The person was trying to find an easy way to find all instances of 3x and replace with 3\x\. Actually, she was trying to do more than that — if she’d only been looking for that, then a normal find/replace should work. For the rest of the string, however, she really needed to use wildcards. Where she was getting stuck was defining the Find correctly, and then the Replace.

Here’s my solution (using wildcards):

• Find: (3)(x) 
• Replace: \1^92\2^92

How this works:

• First, look for 3 followed immediately by x. I separated them in the Find string with parentheses so that I could treat them as separate elements in the Replace string.
• Next, for the replace, type \1 to replace the first element (the 3) with itself, then type ^92 to add a backslash character (you can’t type a \ as that won’t work), then \2 to replace the second element of the Find with itself (i.e. the x), then another ^92 for a final backslash character.

Two things to note:

• The backslash is an escape character in a Find, so if you need to find one, you need to surround it with square brackets and ‘escape’ it — i.e. [\\] in a Find.
• The backslash is a special character in Replace too as it designates the element you want to replace with itself. Instead, you have to use ^92 in place of a \.

 

Word: Switch the number and punctuation order

On another blog post, Peter asked for some help:

I have hundreds of superscript characters (not footnote markers) that have a space before them and punctuation (periods and commas only) after them. I’m trying to delete the space and move the punctuation in front of it.

You can do this using a find/replace with wildcards. However, the instructions below DON’T differentiate between numbers that are superscripted and numbers that aren’t, so it will switch those too. If you don’t have any instances of <space>single ordinary number<period or comma>, then you should be fine. I suggest you try this on a COPY of your document and make sure you get what you want and nothing more, before using it on your main document.

Steps:

1. Press Ctrl+H to open the Find and Replace dialog box.
2. Click More, then select the Use wildcards option.
3. In the Find field, type: ( )([0-9])([.,])
   (Note: There’s a space between the first set of parentheses. Because you have hundreds of these, there’s a good chance that you won’t have just single digit numbers. For multi-digit numbers, type this instead: ( )([0-9]@)([.,])
4. In the Replace field, type: \3\2
5. Click Find Next then click Replace. Repeat.

(Note: Only click Replace All if you are certain that no other ordinary numbers will be affected.)

What you are doing here is looking for a space (item 1), followed by any single digit number (item 2), followed by either a period or a comma (item 3). Then you’re replacing that string with the period or comma (item 3) then the number (item 2).

 

Word: Find and replace multiple spaces after punctuation

You receive a document that has multiple spaces after standard punctuation — periods, commas, semicolons, colons, question marks, and exclamation marks. Sometimes the author used two spaces, sometimes three, sometimes five!

How to fix it?

Well, you can run several find/replace routines but as the number of spaces is unknown and as there are many types of punctuation, that could take quite a lot of time. Instead, you can use a wildcard find/replace routine to find them all at once, keep the punctuation, and replace the unknown number of spaces with a single space. Here’s how (for your own peace of mind, test this on a COPY of your document first):

1. Press Ctrl+H to open the Find and Replace dialog box.
2. Click the More button to show more find/replace options.
3. Select the Use wildcards checkbox.
4. In the Find what field, type: ([,.;:\?\!])( {2,9})
   NOTE: There’s a single space before the {2 — make sure you include that. To be safe, copy the ‘code’ in this step, and paste it into your Find what field.
5. In the Replace with field, type: \1 followed immediately by a single space.
6. Click Find Next to find the first instance, then Replace to replace the multiple spaces with a single space.
7. Repeat step 6 as many times as you need to be confident that it’s finding the right things. Once you’re confident, click Replace All to run through the whole document and fix all instances.

Explanation for how this works:

• ([,.;:\?\!]) looks for any of the listed punctuation characters. Question and exclamation marks are special cases and need to be ‘escaped’ with a \. Because you’re using wildcards, you need to surround the text you want to find in parentheses. This string defines the first section of the Find.
• ( {2-9}) looks for a space followed by two or more spaces, up to 9 spaces (you can put whatever numbers you like inside the curly braces — if you think you might have some instances of punctuation followed by 15 spaces, then change these numbers to {2-20}, for example. Again, this section is surrounded by parentheses to define it as a separate section.
• \1 replaces the first part of the wildcard string with itself. In other words, the punctuation character found is replaced with itself, so no change apparently occurs.
• The space after \1 replaces the multiple spaces found in the second part of the wildcard string with a single space.

Word: Find and highlight multiple words at once

This is a variation on the multiple find/replace macro covered here: https://cybertext.wordpress.com/2015/03/03/word-macro-to-run-multiple-wildcard-find-and-replace-routines/. I suggest you read that post first (and test it) before attempting this one. This post assumes you can already do what’s in that earlier post.

Scenario

The scenario for this one is a little different — the author wanted to find various words in a document and highlight them for probable change. As there were MANY documents she had to process, she needed an easy way to find these words and highlight them. She didn’t want to change them at this stage. The words related to project names, company names, facility names, document number prefixes, etc. One project’s documents were to be the basis of a set of documents for another project in the same company, so one of the tasks was to ‘personalize’ the copies of the original project’s documents with the names used by the new project.

Solution

Although there may be several ways you can do this, I decided to stick with what I already knew. I figured that using a variation of the ReplaceTableWithList macro (as discussed here: https://cybertext.wordpress.com/2015/03/03/word-macro-to-run-multiple-wildcard-find-and-replace-routines/) and a new table called by that macro variation should solve it. And it did.

Step 1: Create your find/replace table

1. Start a new Word document, and create a two-column table in it.
2. In the left column, type in the words/phrases you want to find, each on a different row.
3. In the right column, type \1. (This means that the wildcard find/replace will replace what was found with itself — remember, you’re only trying to identify the words that *may* need changing at this point, not changing them.)
4. Save the document to this folder, noting the name of the file as you’ll need that later:
   C:\Users\<your_username>\AppData\Roaming\Microsoft\Word\STARTUP.

Step 2: Set up the macro to work with the table

Add the macro below to an existing document, or better, an existing template, or even better, a central macros template that loads whenever you open Word.

Once you’ve added it, change the line that starts with sFname and has the file path — that path points to MY file on MY computer. You need to change it to YOUR file name and YOUR file path.

NOTE: Copy all the code below to the clipboard — it goes off the page, so don’t type it out as you’ll miss some of it or could make a typo.

Sub ReplaceFromTableListName()
' from Doug Robbins, Word MVP, Microsoft forums, Feb 2015, based on another macro written by Graham Mayor, Aug 2010
Dim oChanges As Document, oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim rFindText As Range, rReplacement As Range
Dim i As Long
Dim sFname As String
'Change the path in the line below to reflect the name and path of the table document
sFname = "C:\Users\rhonda\AppData\Roaming\Microsoft\Word\STARTUP\find_and_replace_routines_names_macro.docx"
Set oDoc = ActiveDocument
Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
Set oTable = oChanges.Tables(1)

' Make sure highlight is set to the colour you want, e.g. wdYellow, wdBrightGreen, wdPink, wdTurquoise
Options.DefaultHighlightColorIndex = wdTurquoise

For i = 1 To oTable.Rows.Count
 Set oRng = oDoc.Range
 Set rFindText = oTable.Cell(i, 1).Range
 rFindText.End = rFindText.End - 1
 Set rReplacement = oTable.Cell(i, 2).Range
 rReplacement.End = rReplacement.End - 1
 Selection.HomeKey wdStory
 With oRng.Find
 .ClearFormatting
 .Replacement.ClearFormatting
 .MatchWildcards = True
 .Text = rFindText.Text
 .Replacement.Text = rReplacement.Text
 .Replacement.Highlight = True
 .Forward = True
 .Wrap = wdFindContinue
 .Execute Replace:=wdReplaceAll
 End With
Next i
oChanges.Close wdDoNotSaveChanges
End Sub

Step 3: Test that it works

After setting up the ReplaceFromTableListName macro (above), run it on a test document — copy an existing document and test on the copy to make sure you don’t mess up anything.

NOTES:

• If you get an error message, check that you have the correct file name and path in the macro, AND check that your Word document containing the table that’s called by the macro has no empty rows.
• If none of the words in your table get highlighted in your document on the first pass, select a highlight colour from the Home tab as though you were going to highlight manually, then run the macro again.
• If some words are missed, check the table containing them — if a word has an initial capital in the table, but not in the document you are searching (e.g. in a URL), then the macro won’t highlight it. For words that could be capitalized in various ways, either add new lines for each variation, OR search for the part of the word you know won’t be capitalized (e.g. if you were searching for ‘Amazon’, then change the search term in the first column to ‘mazon’ to pick up the word whether it has an initial cap or not).
• Some areas of your document won’t get highlighted, even if they contain the words you’re looking for — e.g. headers and footers, text boxes. You’ll have to check for these manually.

My author was VERY happy — she had something that only took a few seconds to run and highlighted all the various words she needed to look for.

[Links last checked November 2015]