Reverse contents in cell

[Mantle51]

Hello,
This is bit tricky, I have to reverse what's on either side of a " ", but leave anything to the right of a comma alone. And the comma always appears after the words separated by space.

THis works, but seems unnecesarily involved...

Sub Reverse_Spc()
'Each cell begins with two surnames, separated by a space. These names have to be reversed without
'affecting the what's to the right of the comma i.e. first name
Dim i As Long, j As Long, k As Long, varVal As Variant, Sur1 As Variant, Sur2 As Variant, strRemain

For j = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
varVal = Cells(j, 1).Value
For i = 1 To Len(varVal)
If Mid(varVal, i, 1) = "," Then
Exit For
ElseIf Mid(varVal, i, 1) = " " Then
Sur1 = Mid(varVal, 1, i - 1)
For k = i + 1 To Len(varVal)
If Mid(varVal, k, 1) = "," Then
Sur2 = Mid(varVal, i + 1, k - (i + 1))
strRemain = Mid(varVal, k, Len(varVal))
Exit For
End If
Next k
Cells(j, 1).Value = Sur2 & " " & Sur1 & strRemain
End If


Next i

Next j


End Sub

 

[GlennUK]

Have a look at the InStr function, which will find the position of the " " and "," for you, instead of looping.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

OR
you could do it without VBA
=TRIM(MID(A1,FIND(" ",A1),FIND(",",A1)-FIND(" ",A1))) & " "&LEFT(A1,FIND(" ",A1)-1)&", "&TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))

Using trim negates problems with too many spaces. The comma which I've added back in can as easily be removed.

Yep, I'm bored!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

are there any...
[tt]
Van der Voort, Mary Jo
[/tt]
type names?

So is it the SPACE or the COMMA that separates the names?

Sounds to me like COMMA is key.

dim aRev
aRev = Split(YourName,",")
msgbox aRev(1) & " " & aRev(0)

Skip,

_[red][/red]

 

[Mantle51]

Thanks GlennUK,

Here's what I wrote using InStr, however there's one bug I cannot figure with the & Remain...

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




End With
Next
End Sub

 

[Fenrirshowl]

Mantle51

Tried your code and it worked fine for me. However, when I restricted the width of the column, the only visible output was "louis". Try extending wither the row height or the column width to see if that is the problem.

Fen

 

[GlennUK]

Hi Mantle51,

it works for me. Have you got the cell formatted as Right-Aligned by any chance?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Mantle51]

Ah yes, sometimes the simple ones kill you.

Thanks........Mickey

 

[GlennUK]

My pleasure!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.