Help with Excel Formula?
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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|