Hi Gavin,

Am Fri, 18 Dec 2015 22:45:07 -0800 (PST) schrieb GavinS:

In A1 I have the string DOE, JOHN PETER MR (SEE SMITH)

What I would like to do is compose a formula that can reorder this string to read

MR JOHN PETER DOE (SEE SMITH)

However note that MR could be MRS, MS or DR.
That is A1 might contain DOE, JANE SALLY MS (SEE SMITH)

that would be a long formula. Better try it with a macro. For your
examples following code works and writes you the new strings to column
B:

Sub MoveNames()
Dim rngC As Range
Dim varWords As Variant
Dim i As Long, LRow As Long
Dim myStr As String, str1 As String, str2 As String, str3 As String

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
varWords = Split(rngC, " ")
str1 = varWords(UBound(varWords) - 2) & " "

Select Case UBound(varWords)
Case 4
str2 = varWords(1) & " " & varWords(0) & " "
str3 = varWords(3) & " " & varWords(4)
Case 5
str2 = varWords(1) & " " & varWords(2) & " " & varWords(0) & " "
str3 = varWords(4) & " " & varWords(5)
End Select
rngC.Offset(, 1) = Replace(str1 & str2 & str3, ",", "")
Next
End With
End Sub

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional