Help editing a macro for excel

Jeni - Oct 18, 2010 at 10:52 AM
 RayH - Oct 20, 2010 at 11:57 AM

I have a list of names in the form LastName FirstName. For example: Smith John. Most names consist of only one space (between the last and first name); however, some last names contain spaces within it for example: El Dir Hamil, which has a first name of Amir so this appears as El Dir Hamil Amir in the list. I wrote a macro: what I want it to do is take the first name (which appears after the last space) and put it infront of the last name, withouth alterning the last name. So the list sould then appear as FirstName LastName, for example: John Smith and Amir El Dir Hamil. The macro I wrote works fine for names that only contain one space, but for ones that contain more than one space I'm getting a result like this: El Dir Amir Hamil < it takes the first name and only moves it before the "word" that appears before it, but i want the first name to move all the way infront of these three "words".

This is the macro I wrote please help with adjusting it to work as described above:

Public Sub RearrangeName()

Dim i As Long, j As Long, LR As Long
Dim oldname As String, newname As String, nchar1 As String, nchar2 As String
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To LR

oldname = Cells(i, "B").Value
If oldname <> "" Then
newname = Left(oldname, 1)
For j = Len(oldname) To 1 Step -1
nchar1 = Mid(oldname, j, 1)
nchar2 = Mid(oldname, 1, j - 1)
If nchar1 = " " Then
newname = Right(oldname, Len(oldname) - j)
newname = newname & " " & nchar2
End If
Next j

Cells(i, "B") = newname
End If
Next i

End Sub


1 reply

Public Sub RearrangeName2()
Dim i As Long, y As Long, LR As Long
Dim oldname As String, newname As String, str1 As String, str2nchar2 As String

Let LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To LR

Let oldname = Cells(i, "B").Value
Let y = InStrRev(Cells(i, "B"), " ")
Let str1 = Left(Cells(i, "B"), InStrRev(Cells(i, "B"), " ") - 1)
Let str2 = Right(oldname, Len(oldname) - y)
Let newname = str2 + " " + str1

Cells(i, "B") = newname
Next i

End Sub
That worked perfectly. But now i need to alter this formual i wrote which returns the first name in one coloum and the last name in another based on coloum B (which i used to have in the format lastname firstname). The formual i have, but it doesnt return the first and last name properly anymore is: First name in cell C {=LEFT(B2,MAX((MID(B2,ROW($1:$57),1)=" ")*ROW($1:$57)))} Last name in cell D =RIGHT(B2,LEN(B2)-LEN(C2)). I cant figure out how to fix the fomulas so they work with the rearranged colum B (firstname lastname).
Sorry I meant last name in cell C and first name in cell D.
in C: Last Name
=RIGHT(B2,LEN(B2)-SEARCH(" ",B2,1))
in D: First Name
=LEFT(B2,SEARCH(" ",B2,1))