Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 199
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Formula Guidance. Formula need to determine if cell is popul Matt Excel Programming 0 February 19th 10 08:32 PM
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible blue[_2_] Excel Discussion (Misc queries) 2 July 11th 07 06:08 PM
Build excel formula using field values as text in the formula val kilbane Excel Worksheet Functions 2 April 18th 07 01:52 PM
Excel Formula Doesn't Execute (Shows formula-not the calcuation) Keys1970 Excel Discussion (Misc queries) 4 November 15th 06 03:12 PM
Excel 2002 formula displayed not value formula option not checked Dean Excel Worksheet Functions 1 February 28th 06 03:31 PM


All times are GMT +1. The time now is 03:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"