Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

Shortcut key or Macro to swap items in list?

I guess the parameters would be:
item 1 - after the comma up to, but not including the "and"
item 2 - after the "and" up to, but not including the ending punctuation.

before: item 1 and item 2.
after: item 2 and item 1.

Swap thusly in one key stoke instead of two highlight point and drag moves:  it’s all about getting the ‘and’ re-situated after swamping the nouns.  There really ought to be a way considering how often people have to do this.

Before:
In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins, date pieces and sunflower seeds.

After:
In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins, sunflower seeds and date pieces.

 
0
Alex Campbell
Asked:
Alex Campbell
  • 3
  • 2
1 Solution
 
GrahamSkanRetiredCommented:
Obviously, the principle would be to save the first item somewhere (in a variable, perhaps), delete it from the last, and then add the saved item after the next one.

For more specific advice, I think that we need to know the structure of the list. Is it an item in a VBA ListBox, or perhaps some text in an Excel cell or in a Word document, or something else?
0
 
Alex CampbellAuthor Commented:
Just in a Word document in a simple sentence as in the example:

Before:
In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins, date pieces and sunflower seeds.

After:
In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins, sunflower seeds and date pieces.



0
 
Chris BottomleyCommented:
One example would be to call:


swapand  "In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins, date pieces and sunflower seeds."

which returns

In my home made trail mix there are:  pumpkin seeds, walnuts, sugar roasted fennel seeds, raisins,sunflower seeds and date pieces.

Chris
Function swapAnd(str As String) As String
Dim arr As String
Dim bolStop As Boolean
Dim posAnd As Long
Dim strtemp As String
Dim arrLeft() As String
Dim strLeft As String
Dim strRight As String

    bolStop = CBool(Right(str, 1) = ".")
    If bolStop Then str = Left(str, Len(str) - 1)
    posAnd = InStrRev(str, " and ")
    strRight = Trim(Right(str, Len(str) - posAnd - 4))
    If posAnd = 0 Then
        swapAnd = str
        Exit Function
    End If
    arrLeft = Split(Trim(Left(str, posAnd)), ",")
    strLeft = Trim(arrLeft(UBound(arrLeft)))
    arrLeft(UBound(arrLeft)) = ""
    swapAnd = Join(arrLeft, ",") & strRight & " and " & strLeft
    If bolStop Then swapAnd = swapAnd & "."
    
End Function

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Alex CampbellAuthor Commented:
How would I use it in Word?

I opened the Macro Editor and pasted it, but when i go to run it, swapand does not show up.
0
 
Chris BottomleyCommented:
Slight change in direction then ... use the following which operates on the selected text.

Chris
Sub swapAnd()
Dim str As String
Dim arr As String
Dim bolStop As Boolean
Dim posAnd As Long
Dim strtemp As String
Dim arrLeft() As String
Dim strLeft As String
Dim strRight As String
Dim newStr As String

    str = Selection.Text
    bolStop = CBool(Right(str, 1) = ".")
    If bolStop Then str = Left(str, Len(str) - 1)
    posAnd = InStrRev(str, " and ")
    strRight = Trim(Right(str, Len(str) - posAnd - 4))
    If posAnd = 0 Then
        Exit Sub
    End If
    arrLeft = Split(Trim(Left(str, posAnd)), ",")
    strLeft = Trim(arrLeft(UBound(arrLeft)))
    arrLeft(UBound(arrLeft)) = ""
    newStr = Join(arrLeft, ",") & strRight & " and " & strLeft
    If bolStop Then newStr = newStr & "."
    Selection.TypeBackspace
    Selection.TypeText Text:=newStr
    
End Sub

Open in new window

0
 
Alex CampbellAuthor Commented:
cool
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now