Switching FIRST MID LAST to LAST FIRST MID

Posted by jim byrne on October 12, 2001 11:43 AM

Does anyone know how to take a column which is in the following format, FIRST MIDDLE LAST NAME and switch the order to LAST, FIRST MIDDLE.

I tried using the text to column command but i get some names without a middle name in the middle name column.

Thanks

Jim

Posted by Todd on October 12, 2001 11:57 AM

something like this should work, if what you need switched is in A1:

=right(a1,len(a1)-search(" ",a1,1+search(" ",a1))&", "&left(a1,search(" ",a1,1+search(" ",a1)))

note the nested searches skip the first space.

Posted by Aladin Akyurek on October 12, 2001 12:34 PM

Hmmm. I don't know. I copied your code and tried it out and it worked for me. Here's the actual code I used: I shortened the loop for my test, and I renamed a worksheet to "Item Master" and then ran this: I had all sorts of stuff in the 20 cells: formulas, text, boolean, date, numeric, and they all came through as written:

Sub OutsideCounter()

Dim OutsideCounter As Integer
Dim formulaId2

For OutsideCounter = 7 To 27 ' 5000

Worksheets("Item Master").Select
formulaId2 = Range("J" & OutsideCounter).Value

Debug.Print formulaId2

Next OutsideCounter

End Sub

So I can't say why it didn't work for you.