ExcelBanter - View Single Post

Victor Delta[_2_]

In article , says...

Hi Victor,

Am Fri, 14 Aug 2015 20:56:40 +0100 schrieb Victor Delta:

=IF(ISBLANK(A4),"",CONCATENATE(RIGHT(A4,LEN(A4)-FIND(" ",A4)),", ",LEFT
(A4,FIND(" ",A4)-1)))

which turns 'Peter Smith' in col A into 'Smith, Peter' in col B -
enabling me to sort the list alphabetically by surname.

However, I've recently added some names to the list which have a title
e.g. Cllr Peter Smith. Now the formula results in 'Peter Smith, Cllr'
which mucks up the alphabetic sorting!

try it with this UDF:

Function myNames(myRng As Range) As String
Dim varName As Variant

varName = Split(myRng, " ")
If UBound(varName) = 1 Then
myNames = varName(1) & ", " & varName(0)
ElseIf UBound(varName) = 2 Then
myNames = varName(2) & ", " & varName(1) & " " & varName(0)
End If
End Function

and call the function into the sheet with:
=myNames(A1)

Regards
Claus B.

Many thanks, that's very helpful. I was hoping it might be possible to
make the split on the last (of n) spaces to cope with the 'Cllr Peter M
Smith' who may crop up one day...!