switching text around within a cell in excel

  oo7juk 00:34 06 Mar 2007
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 2007

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 2007

With the name in A1

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

  VoG II 10:12 06 Mar 2007

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

  VoG II 10:41 06 Mar 2007

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 2007

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 2007

Thanks to all, worked perfect.

  John B 22:48 06 Mar 2007

marked for future reference!

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

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?