Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2019, 09:50 AM
cheech1981 cheech1981 is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Advanced Beginner
Reversing Word Order with Multiple String Types
 
Join Date: Nov 2011
Location: New Jersey, USA
Posts: 77
cheech1981 is on a distinguished road
Default Reversing Word Order with Multiple String Types

Hi all,



I am doing a find/replace to reverse the first initial and last name in a list, like this:

J. Smith --> Smith, J.

I've got that down with:
Find: <([A-Z][! ]@) ([A-Z][! ]@)>
Replace: \2, \1

However, I quickly stumbled across a challenge. Some names have both a first and middle initial, such as

R. M. Johnson --> Johnson, R. M.

Is there a single find/replace query I can run that would account for both types of names (names with just a first initial and names with both first and middle initials)?

Thank you for any ideas you can provide!
Reply With Quote
  #2  
Old 06-25-2019, 10:57 AM
kilroy kilroy is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

If you run this one first you should be ok running the one you posted second.

find: <([A-Z][. ] [A-Z][. ]) ([A-Z][! ]@)>
replace: \2, \1
Reply With Quote
  #3  
Old 06-26-2019, 08:27 PM
cheech1981 cheech1981 is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Advanced Beginner
Reversing Word Order with Multiple String Types
 
Join Date: Nov 2011
Location: New Jersey, USA
Posts: 77
cheech1981 is on a distinguished road
Default

EDIT

Great Idea Kilroy. I was able to run your idea as a replace all to significantly decrease the work.

Now I'm going through one by one with the find-replace in the OP to reverse the rest. If anyone is doing in this in the future, you can't hit "replace all" in the second run for a few reasons. For instance, you will have to manually skip instances where it will catch "F., Last" (that is, the first initial from a previous name and the last name of the next author). You will also have to manually handle names that have triple initials, hyphenated last names, and names with multiple parts (e.g., "van something," "de something").

Looks like I would have to write some code in a macro to handle these special instances. For now the provided solution was quite useful to knock out a good chunk, and the find-replace in the OP at least lets me move quickly from instance to instance, skipping over the irrelevant ones and fixing the others. Thanks!

Last edited by cheech1981; 06-27-2019 at 09:17 AM. Reason: updated response after playing with it a bit
Reply With Quote
  #4  
Old 06-27-2019, 09:53 AM
kilroy kilroy is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

Try this:

Find: ([A-Z]. [A-Z]. )([A-Z]{1}[a-z]{1,}-[A-Z]{1}[a-z]{1,})------(works for R. M. Johnson-Hulk – run first)
Replace: \2, \1

Find: ([A-Z]. )([A-Z]{1}[a-z]{1,}-[A-Z]{1}[a-z]{1,}) -----(worksfor M. Johnson-Hulk –run second)
Replace: \2, \1

Find: ([A-Z][. ]) ([A-Z][. ]) ([A-Z]{1}[a-z]{1,}) ----(Worksfor R. M. Johnson – run third)
Replace: \3, \1, \2

Find: ([A-Z]. )([A-Z]{1}[a-z]{1,}) -----(Works for J. Smith– run last)
Replace: \2, \1


Last edited by kilroy; 06-27-2019 at 01:15 PM. Reason: spacing
Reply With Quote
  #5  
Old 06-27-2019, 01:12 PM
kilroy kilroy is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

Code wrap tags not working for me. I'm sure the pro's are shaking their head. Lol. It can probably be written much better but not by me.


Try this:


Sub TwoInitialsTwoLastNames()
Selection.WholeStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = " ([A-Z]. [A-Z]. )([A-Z]{1}[a-z]{1,}-[A-Z]{1}[a-z]{1,})"
.Replacement.Text = " \2, \1"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Selection.Find.Execute Replace:=wdReplaceAll
End With
Call OneInitialTwoLastNames
End Sub
Sub OneInitialTwoLastNames()
Selection.WholeStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([A-Z]. )([A-Z]{1}[a-z]{1,}-[A-Z]{1}[a-z]{1,})"
.Replacement.Text = "\2, \1"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Selection.Find.Execute Replace:=wdReplaceAll
End With
Call TwoInitialsOneLastName
End Sub
Sub TwoInitialsOneLastName()
Selection.WholeStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([A-Z][. ]) ([A-Z][. ]) ([A-Z]{1}[a-z]{1,})"
.Replacement.Text = "\3, \1, \2"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Selection.Find.Execute Replace:=wdReplaceAll
End With
Call OneInitialOneLastName
End Sub
Sub OneInitialOneLastName()
Selection.WholeStory
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "([A-Z]. )([A-Z]{1}[a-z]{1,})"
.Replacement.Text = "\2, \1"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
Selection.Find.Execute Replace:=wdReplaceAll
End With
End Sub
Reply With Quote
  #6  
Old 07-16-2019, 04:14 PM
cheech1981 cheech1981 is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Advanced Beginner
Reversing Word Order with Multiple String Types
 
Join Date: Nov 2011
Location: New Jersey, USA
Posts: 77
cheech1981 is on a distinguished road
Default

Kilroy thank you so much for your ideas. Really cool. I had already done the work manually before checking back again; however, I will be sure to use these moving forward. Thank you again for the time and thought you put into the code and take care for now.
Reply With Quote
  #7  
Old 07-17-2019, 06:08 AM
kilroy kilroy is offline Reversing Word Order with Multiple String Types Windows 10 Reversing Word Order with Multiple String Types Office 2016
Competent Performer
 
Join Date: Sep 2016
Location: Southern Ontario
Posts: 118
kilroy is on a distinguished road
Default

No problem Cheech.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I change multiple pictures in Word 2016 to all new pictures that appear in a specified order? chaz Word VBA 7 07-11-2018 03:57 AM
Reversing Word Order with Multiple String Types Replace multiple strings by one same string at once puff Word 2 02-28-2018 11:04 AM
How to do multiple find and replace in string. PRA007 Word VBA 2 01-06-2016 09:10 PM
multiple types of alignment on one line? uoficowboy Word 1 03-06-2010 08:54 PM
Reversing Word Order with Multiple String Types Reversing Text Order weemikey Word 4 08-05-2009 11:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:04 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft