switching text around within a cell in excel

  oo7juk 00:34 06 Mar 07


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


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.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…