Switching the order of names macro

[Yoxy99]

Hello, I have a column in excel that contains the first name middle, middle initial, and last name. I need the order of this to be changed with a macro. The current format of the cell is: Smith John M and I would like the format to be John M Smith does anyone know of a way to do this. I have a function that does this but I need a macro. Thanks.

 

[CautionMP]

I have a function that does this but I need a macro.

Could you clairfy? Are you looking for macro that allows you to invoke the function you currently have on a range of cells without having to paste the existing function in the current worksheet?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Hi,

This is not a change in FORMAT, it is a change in DATA.

You need to PARSE your data into separate columns; you ought not to have all the data in one column.

This task, may or may not be simple, depending how consistent your data is. Names like St John, no middle initial, Jr, Sr etc., might make the parsing more difficult.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Yoxy99]

The function I am using is =MID(A1:A5000&" "&A1:A5000, FIND(" ",A1:A5000)+1,LEN(A1:A5000)) and I tried using it in a macro this way:


Sub InsertFormula()

Worksheets("Sheet1").Range("A1:A5000").Formula = "=MID(A1:A5000, FIND(Space(1),A1:A5000)+1,LEN(A1:A5000))"

End Sub

But it got a compile error. Also the data is not extremely large and will stay consistent so any Jr's Sr's or St Johns should not be a big deal.

 

[ck1999]

If you want to change the data like skip mentioned try

Sub InsertFormula()

   For counter = 1 To 5000
        vName = Range("a" & counter)
        Lname = Left(vName, InStr(vName, " ") - 1)
        vname = Right(vName, Len(vName) - InStr(vName, " "))
        Range("b" & counter) = Fname & " " & mname & " " & Lname
        
        
    
   
   Next counter
   
End Sub

this gets you going in the right direction you will need to determine fname(first name) and mname(middle name)

It works for me.

Also you post earlier makes a circular reference. Although that is not the error it will not work.

ck1999

 

[Bong]

If you are guaranteed that your data will comprise 3 pieces separated by "space", you can use the split function.
Thus, strArr = split("Smith John M"," ")
will populate the array, strArr.
Then, strArr(1) & " " & strArr(2) & " " & strArr(0)
will be "John M Smith"

_________________
Bob Rashkin