macro to reorder a cell.

I have a file that I receive daily in which Column A is in this format:

xxxxxxxx FirstName LastName

(xxxxxxxx is account number)

I need to build a macro to automatically convert all of column A into:

xxxxxxxx LastName, FirstName


Basically, it needs to swap the first and last name, and add the comma.

Can anyone help me out on this? I think I could figure out how to do it with the CONCATENATE operator, but I'm not sure how to split the first cell with a macro. Any help would be greatly appreciated. Thank You.

Hello DAKpluto,

This is done so much easier using a VBA macro.

Sub ReOrder()

  Dim Cell As Range
  Dim Data As Variant
  Dim LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
      For Each Cell In Range(Cells(1, "A"), Cells(LastRow, "A"))
        Data = Split(Cell)
        Cell = Data(0) & Data(2) & ", " & Data(1)
      Next Cell

End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross