Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Excel Formula?
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Excel Formula?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formula Guidance. Formula need to determine if cell is popul | Excel Programming | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions |