Field containing FirstName Lastname need to reverse them

[Pat]

I know nothing about Excel.
A client has asked me if i can swap the names around.
I don't even know where to do the VBA code behind excel.
Please be gentle with me.

[Rick Rothstein]

When asking a question on a forum or in a newsgroup, you must keep one thing in mind... the people you are asking to help you know absolutely nothing about your workbook, setup or situation... so you have to tell us everything that is relevant. Some off-the-top-of-my-head questions might be...

Is each name in a single cell or are they split into columns? Are there middle names and/or initials involved? Where are they located? Where did you want them to end up at... with the after the first or last name? After the names are reversed, what did you want separating them... a comma perhaps? If the whole name is in a single cell, are you looking to physically change that name within the cell, or duplicate it with the names reversed in another cell?

I'm sure there are other relevant things about your worksheet that I haven't asked above, so you need to think what is relevant and tell us. Also, showing us some before and after examples would we helpful. Even more helpful would be if you could post the workbook itself as an attachment to your next message, assuming you are able to share the data with the entire world without compromising anyone's privacy. If privacy forbids your doing so, a workbook with made up data replacing your actual data would work as long as your made up data was representive of your actual data.

[Pat]

Thanks Rick, I didn't know much of what they wanted initially, but it seems they have put peoples names into the one cell and want to reverse them in the same cell. So the names are defined as Firstname Lastname pairs with a space between them. By reversing i mean keep the same format with a space between them.
Unfortunately i cannot give you a sheet as it's private data.

[HansV]

First make a backup copy of the workbook.
Open the workbook.
Press Alt+F11 to activate the Visual Basic Editor.
Select Insert | Module.
Copy the following macro into the module.

Code: Select all

Sub SwitchFirstLast()
  ' Modify as needed
  Const strCol = "A"
  Const lngFirst = 2
  ' Variables
  Dim lngRow As Long
  Dim lngLast As Long
  Dim strParts() As String
  ' Speed up macro by not updating the screen
  Application.ScreenUpdating = False
  ' Last used row
  lngLast = Range(strCol & Rows.Count).End(xlUp).Row
  ' Loop
  For lngRow = lngFirst To lngLast
    ' Split name
    strParts = Split(Range(strCol & lngRow).Value, " ")
    If UBound(strParts) = 1 Then
      ' Assemble in reverse order
      Range(strCol & lngRow).Value = strParts(1) & " " & strParts(0)
    End If
  Next lngRow
  Application.ScreenUpdating = True
End Sub

Change the two constants: strCol is the column containing the names, and lngFirst is the first row to be processed. The macro determines the last row to be processed automatically.
You can run the macro from the Visual Basic Editor by clicking in it and pressing F5, or from within Excel by pressing Alt+F8 to activate the Macros dialog, then select the macro in the list and click Run.

[Rick Rothstein]

Okay, as long as there are no middle names or initials AND there are no spaces in either the first names (such as "Mary Anne") or in the last names (such as "da Vinci"), then try this. Press ALT+F11... this will take you into the VB editor. Once there, click Insert/Module on its menu bar. When you do this, a window will open up. Copy/paste the following code into it...

Code: Select all

Sub ReverseNames()
  Dim N As Range
  For Each N In Selection
    N.Value = Mid(N.Value & " " & N.Value, InStr(N.Value, " ") + 1, Len(N.Value))
  Next
End Sub

That is it. Now go back to the worksheet with your names on it and select all the cells with those names. Next, press ALT+F8 and select ReverseNames on the list that appears, then click the Run button. The names in the cells you selected should now be reversed.
 

[Pat]

Thank you Hans, i will try that tomorrow at work.