Switching Text

F

Felix

I have a huge list of names in either of the following ways:

PeterSmith or
Peter Smith

Is there an easier way (other than manually switch) to have the list in the
order like

Smith, Peter or at least
Smith Peter ?

The only way I can think of is to somehow recognize the capitals...

Thank you

Felix
 
B

Bob Phillips

Here's some code

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long, k As Long
Dim iLastRow As Long
With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
k = Len(.Cells(i, "A").Value)
For j = k To 2 Step -1
If Mid(.Cells(i, j).Value, 1) = " " Or _
Mid(.Cells(i, "A").Value, j, 1) = UCase(Mid(.Cells(i,
"A").Value, j, 1)) Then
.Cells(i, "A").Value = Right(.Cells(i, "A").Value, k - j
+ 1) & _
", " & Left(.Cells(i, "A").Value, j - 1)
Exit For
End If
Next j
Next i

End With

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
F

Felix

Hi Bob,

I tried to copy to the code. All of the following part comes out in red:


If Mid(.Cells(i, j).Value, 1) = " " Or _
Mid(.Cells(i, "A").Value, j, 1) = UCase(Mid(.Cells(i,
"A").Value, j, 1)) Then
.Cells(i, "A").Value = Right(.Cells(i, "A").Value, k - j
+ 1) & _
", " & Left(.Cells(i, "A").Value, j - 1)



What do I do wrong?


Felix
 
D

Dave Peterson

Bob's code got hit by a line wrap problem in the newsgroup:

Option Explicit
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long, k As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
k = Len(.Cells(i, "A").Value)
For j = k To 2 Step -1
If Mid(.Cells(i, j).Value, 1) = " " _
Or Mid(.Cells(i, "A").Value, j, 1) _
= UCase(Mid(.Cells(i, "A").Value, j, 1)) Then
.Cells(i, "A").Value _
= Right(.Cells(i, "A").Value, k - j + 1) & _
", " & Left(.Cells(i, "A").Value, j - 1)
Exit For
End If
Next j
Next i
End With
End Sub
 
R

Ron Rosenfeld

I have a huge list of names in either of the following ways:

PeterSmith or
Peter Smith

Is there an easier way (other than manually switch) to have the list in the
order like

Smith, Peter or at least
Smith Peter ?

The only way I can think of is to somehow recognize the capitals...

Thank you

Felix
Click to expand...

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this regular expression formula:

=REGEX.SUBSTITUTE(A1,"([A-Z][a-z]+).*?(\w+)","[2], [1]")

PeterSmith Smith, Peter
Peter Smith Smith, Peter

If there is more variability in the data than what you describe, some changes
in the regular expression may be necessary.
--ron
 
Top