Help with Excel Formula?
 

I have a spreadsheet (Excel 2003) in which column A contains a list of
names (known name + surname). In col B is the following formula:

=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!

I can see the problem - the find function is finding the first space
rather than the second when there are two. If only there were a way of
searching right to left but, if there is, I haven't been clever enough
to find it yet. Or perhaps there is another way altogether to tackle
this issue.

I'd be very grateful if anyone has any bright ideas. Thanks.

V

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

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

Help with Excel Formula?
 

Hi Victor,

Am Freitag, 14. August 2015 21:56:30 UTC+2 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)

Kind Regards
Claus

Help with Excel Formula?
 

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...!

Help with Excel Formula?
 

Hi Victor,

Am Sat, 15 Aug 2015 09:40:08 +0100 schrieb Victor Delta:

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...!

to find always the last part of the string try:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)


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

Help with Excel Formula?
 

In article , says...

Hi Victor,

Am Sat, 15 Aug 2015 09:40:08 +0100 schrieb Victor Delta:

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...!

to find always the last part of the string try:
=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)


Regards
Claus B.

Claus

Thanks again,

V

Help with Excel Formula?
 

Hi Victor,

Am Sat, 15 Aug 2015 11:26:00 +0100 schrieb Victor Delta:

Thanks again,

I don't know which output you need.
You can also try this formula:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",))=1,MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1),MID(A1,FIND("#",SUBSTITUTE( A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)&", "&SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1, " ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),""))


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

Help with Excel Formula?
 

In article , says...

Hi Victor,

Am Sat, 15 Aug 2015 11:26:00 +0100 schrieb Victor Delta:

Thanks again,

I don't know which output you need.
You can also try this formula:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",))=1,MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1),MID(A1,FIND("#",SUBSTITUTE( A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99)&", "&SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1, " ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),""))


Regards
Claus B.

Thanks, that's perfect.

V