PDA

View Full Version : Reversing some contents of cell



bassman71
09-25-2006, 07:07 PM
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.



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

Thanks .............:banghead:

Cyberdude
09-25-2006, 08:09 PM
Hi, bassman! Here's a little something for you to try:
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

lucas
09-25-2006, 08:32 PM
Bassman71,
I tried your code and got:
Contido Morales, Louis

see attached

bassman71
09-26-2006, 01:16 AM
Very curious, I wonder why it didn't work on my PC at work.
Thanks for the input.

Rich

Bob Phillips
09-26-2006, 01:58 AM
Hi, bassman! Here's a little something for you to try:
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

Why the extra arrays?



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

mdmackillop
09-26-2006, 11:37 AM
As a convenient function, paste the code in a standard module and enter =SortIt(A1) in a cell.

Function SortIt(Data As Range)
Dim ary1
ary1 = Split(Replace(Data, ",", ""), " ")
SortIt = ary1(1) & " " & ary1(0) & ", " & ary1(2)
End Function