switching text around within a cell in excel

  oo7juk 00:34 06 Mar 07
Locked

Hi,

I have a list of names starting with surname that I would like to change around. For example Smith John, want to change it to John Smith within the same cell, thanks.

  Taff™ 02:23 06 Mar 07

Insert a column to the right of the existing one. Select the column with the original data and go to Data>Text to Columns and select delimited tect, next and tick the delimiter as "space". This will split the first name and last name assuming that each name consists of ONLY two words. Now cut and paste the surname column to the left of the first name column.

click here and download ASAP Utilities. Install and reboot the computer. Select both columns and go to the ASAP menu select Columns/Rows and merge data. Put either a comma or a space in the first box and you have the result.

Might be a long way round but VOG™ is probably asleep by now!

  VoG II 10:01 06 Mar 07

With the name in A1

=RIGHT(A1,LEN(A1)-FIND(" ",A1)) & " " & LEFT(A1,FIND(" ",A1)-1)

  VoG II 10:12 06 Mar 07

P.S. this cannot be done within the same cell without VBA code.

  VoG II 10:41 06 Mar 07

With code, assuming column A:

Sub rvrs()
Dim LastRow As Long, i As Long, nm As String, fst As String, lst As String, N As Integer
LastRow = Columns(1).Find("*", searchdirection:=xlPrevious).Row
For i = 1 To LastRow
nm = Cells(i, 1).Value
N = InStr(nm, " ")
lst = Left(nm, N - 1)
fst = Right(nm, Len(nm) - N)
nm = fst & " " & lst
Cells(i, 1).Value = nm
Next i
End Sub

  VoG II 10:56 06 Mar 07

Neater:

Sub rvrs2()
Dim LastRow As Long, i As Long, x() As String
LastRow = Columns(1).Find("*", searchdirection:=xlPrevious).Row
For i = 1 To LastRow
x = Split(Cells(i, 1).Value, " ")
Cells(i, 1).Value = x(1) & " " & x(0)
Next i
End Sub

  oo7juk 22:30 06 Mar 07

Thanks to all, worked perfect.

  John B 22:48 06 Mar 07

marked for future reference!

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5