Reversing some contents of cell

[bassman71]

I have the following that reverses what's on either side of a " " , but leaves everything else alone. The reversing part works OK but I cannot figure out how to piece it back together using the & operator. Explained in comment.


[VBA]
Sub Macro1()
'cell shows "Morales Contido, Louis"
'and I'm trying to get "Contido Morales, Louis"
'the code is almost there, I can reverse the names OK,
'however when I add the '& Remain' to the code after the Rev1 Rev2, it populates the cell "Louis"

Dim i As Integer, j As Integer, k As Integer


LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LastRow To 1 Step -1
With Cells(i, 1)

j = InStr(.Value, " ") 'equals 8
k = InStr(.Value, ",") 'equals 16

Rev1 = Mid(.Value, j + 1, (k - 1) - j)
Rev2 = Mid(.Value, 1, j - 1)
Remain = Mid(.Value, k + 1) 'this one is dogging me

Cells(i, 2).Value = Rev1 & " " & Rev2 & "," & Remain
' if I leave off the &
'Remain, it shows up "Contido Morales," but after adding
'& Remain, cell shows up as "Louis"




End With
Next
End Sub
[/VBA]
Thanks .............

[Cyberdude]

Hi, bassman! Here's a little something for you to try:
[vba]Sub UberReverse()
Dim ary1, ary2, ary3, Temp As String
Const OrigStr$ = "Morales Contido, Louis"
Temp = Replace(OrigStr, ",", "")
ary1 = Split(Temp, " ")
ary2 = Split(ary1(0), " ")
ary3 = Split(ary1(1), ",")
MsgBox ary3(0) & " " & ary1(0) & ", " & ary1(2)
End Sub[/vba]

[lucas]

Bassman71,
I tried your code and got:
Contido Morales, Louis

see attached

[bassman71]

Very curious, I wonder why it didn't work on my PC at work.
Thanks for the input.

Rich

[Bob Phillips]

Hi, bassman! Here's a little something for you to try:
[vba]Sub UberReverse()
Dim ary1, ary2, ary3, Temp As String
Const OrigStr$ = "Morales Contido, Louis"
Temp = Replace(OrigStr, ",", "")
ary1 = Split(Temp, " ")
ary2 = Split(ary1(0), " ")
ary3 = Split(ary1(1), ",")
MsgBox ary3(0) & " " & ary1(0) & ", " & ary1(2)
End Sub[/vba]

Why the extra arrays?

[vba]

Sub UberReverse()
Dim ary1
Const OrigStr$ = "Morales Contido, Louis"
ary1 = Split(Replace(OrigStr, ",", ""), " ")
MsgBox OrigStr$ & vbNewLine & ary1(1) & " " & ary1(0) & ", " & ary1(2)
End Sub
[/vba]

[mdmackillop]

As a convenient function, paste the code in a standard module and enter =SortIt(A1) in a cell.
[vba]
Function SortIt(Data As Range)
Dim ary1
ary1 = Split(Replace(Data, ",", ""), " ")
SortIt = ary1(1) & " " & ary1(0) & ", " & ary1(2)
End Function

[/vba]