Rearranging first name and surname in Excel

[EleanorSykes1989]

Hi there,

I have a list of names in excel in the below format; however I need to re-arrange the cells to read "Colin Smith", (dropping the ,).

[TABLE="width: 160"]
<tbody>[TR]
[TD]Smith, Colin

Is there a simple way of doing this please?

Many thanks [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Kyle123]

How about:

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

 

[FormR]

And just another option...

Excel 2013/2016
	A	B
1	Smith, Colin	Colin Smith
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

 

[Scott T]

Where the name is in A1

=TRIM(MID(A1,SEARCH(",",A1)+1,999))&" "&LEFT(A1,SEARCH(",",A1)-1)

 

[EleanorSykes1989]

Many thanks all. I tried the Kyle123 option and this worked perfectly!

 

[JLGWhiz]

VBA for kicks. It compensates for middle names and initials.

Sub switch()
Dim txt As Variant, c As Range
 For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    txt = Split(c.Value, " ")
    c = txt(UBound(txt)) & ", " & txt(LBound(txt))
    If UBound(txt) > 1 Then
        c = c & " " & txt(1)
    End If
 Next
End Sub