Je pense qu'il y avait une erreur dans l'énoncé ... entre A3 et A6 !
2 solutions (et il y en a d'autres, mais je préfère la seconde) :
Sub transpose()
Dim i%
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 2).FormulaR1C1 = _
"=IF(LEFT(RC[-1],4)=""Mme "", MID(RC[-1],5,255), IF(LEFT(RC[-1],3)=""M. "", MID(RC[-1],4,255), RC[-1]))"
Next
End Sub
Sub autre_solution()
Dim i%
For i = 5 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 2).Value = Replace(Replace(Cells(i, 1).Value, "M. ", ""), "Mme ", "")
Next
End Sub