Thread: Reorder elements a cell using arrays, split & join

In Excel, I work with lists that look like this:

Smith: My Homeland, Collected Poems
Smith: Dr. Cuteroff, Collected Poems
Smith: Snark, Collected Poems
Chiu: Always, Anthology Of New American Literature
Jones: Our Way To start The Day, Humble Pie
Smith: Looking In
Zhdenka: Interstices, Anthology Of New American Literature

The format is generally Author: WOrk, Publication. Sometimes there is no Publication.

I would like to be able to select the cells with these works and run a macro that changes the order to:

Author: Publication - Work

like this:

Smith: Collected Poems - My Homeland
Smith: Collected Poems - Dr. Cuteroff
Smith: Collected Poems - Snark
Chiu: Anthology Of New American Literature - Always
Jones: Humble Pie - Our Way To start The Day
Smith: Looking In
Zhdenka: Anthology Of New American Literature - Interstices


I have done some VBA stuff in the past, but nothing with split, join and arrays, which I think may be needed here. Any suggestions, advice or links will be greatly appreciated.

Thanks,
k8

Based on the examples - perhaps something along the lines of:

Sub Example()
    Dim v1 As Variant, vTemp As Variant
    Dim lngD1 As Long, lngD2 As Long
    Dim strTemp As String
    With Selection
        v1 = .Value
        For lngD2 = LBound(v1, 2) To UBound(v1, 2) Step 1
            For lngD1 = LBound(v1, 1) To UBound(v1, 1) Step 1
                vTemp = Split(v1(lngD1, lngD2), ":")
                v1(lngD1, lngD2) = vTemp(0)
                vTemp = Split(vTemp(1), ",")
                If UBound(vTemp) < 1 Then
                    strTemp = vTemp(0)
                Else
                    strTemp = vTemp(1) & " -" & vTemp(0)
                End If
                v1(lngD1, lngD2) = v1(lngD1, lngD2) & ":" & strTemp
            Next lngD1
        Next lngD2
        .Value = v1
    End With
End Sub

I don't really understand what you want to happen where colon does not exist, assuming skip, then either:

vTemp = Split(v1(lngD1,lngD2),":")
If UBound(vTemp) > 0 Then
    v1(lngD1,lngD2) = vTemp(0)
    vTemp = Split(vTemp(1),",")
    'etc...
End If

or

If InStr(v1(lngD1,lngD2),":") > 0 Then
    vTemp = Split(v1,":")
    v1(lngD1,lngD2) = vTemp(0)
    vTemp = Split(vTemp(1),",")
    'etc....
End If

or everything in a oneliner:

Sub snb()
  If InStr(ActiveCell, ":") > 0 Then ActiveCell = Split(ActiveCell, ":")(0) & ":" & Split(Split(ActiveCell, ":")(1), ",")(1) & " - " & Split(Split(ActiveCell, ":")(1), ",")(0)
End Sub

@snb, I can't help but laugh
(I would make the point we're also working with the Selection rather than the ActiveCell but it's of little relevance)

@DO that should be our aim the whole year long....

Sub snb2()
  sq = Selection

  For j = 1 To UBound(sq)
    If InStr(sq(j, 1), ":") > 0 And InStr(sq(j, 1), ",") > 0 Then sq(j, 1) = Split(sq(j, 1), ":")(0) & ":" & Split(Split(sq(j, 1), ":")(1), ",")(1) & " - " & Split(Split(sq(j, 1), ":")(1), ",")(0)
  Next

  Selection = sq
End Sub