 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006NEED TO FIND FIRST " " FROM RIGHT
Help - I have a column in excel that contains names, such as: Joe Smith Joe E. Smith Joe Edward Smith, esq. etc I need to isolate the LAST WORD in the column, for example: Smith Smith esq. The only way I know how to do this is to search for the first " " - but I need to start the search from the RIGHT, not the LEFT. As far as I know, FIND function starts the search from the right... which isn't what I want. Can someone please tell me how to accomplish this task. THANK YOU =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) > Help - I have a column in excel that contains names, such as: > [quoted text clipped - 17 lines] > > THANK YOU  SignatureDave Peterson Thanks Dave - I have NO idea how this works, but it works > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1) > -LEN(SUBSTITUTE(A1," ",""))))) [quoted text clipped - 20 lines] > > > > THANK YOU I stole it from Peo Sjoblom. I'm not sure from whom he stole it <bg>. but if you break it down into smaller pieces, it's not too bad. Say you have "Joe Edward Smith, esq." in A1. =substitute(a1," ","") returns JoeEdwardSmith,esq. (all the spaces are gone) =len(substitute(...)) will give 19 and len(a1) will give 22 Then =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")) is the same as: =SUBSTITUTE(A1," ","^^",22-19) or =SUBSTITUTE(A1," ","^^",3) So this says to replace the 3rd space character with ^^. So =SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")) is essentially just doing: This portion: =FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) will return 18 since ^^ is the 18th (and 19th) character in that string: Joe Edward Smith,^^esq. (go ahead and count, I did!) So it's really just saying: =right(a1,len(a1)-18) or =right(a1,22-18) or =right(a1,4) which is esq. Would I have come up with this? Heck no! But lots of people have and I've stolen their answers. > Thanks Dave - I have NO idea how this works, but it works > [quoted text clipped - 26 lines] > > > > Dave Peterson SignatureDave Peterson He either: 1) created it himself (likely) 2) stole it from me (also likely) 3) stole if from someone else (less likely) -- Bob Umlas >I stole it from Peo Sjoblom. I'm not sure from whom he stole it <bg>. > [quoted text clipped - 73 lines] >> > >> > Dave Peterson Could have come from John Walkenbach, too. He has an explanation of how to build the formula: http://www.j-walk.com/ss/excel/usertips/tip079.htm > He either: > 1) created it himself (likely) [quoted text clipped - 84 lines] > > > > Dave Peterson SignatureDave Peterson It seems no one wants to believe me when I say that it came from Leo Heuser, way back in 2001! http://tinyurl.com/g2ca7 http://tinyurl.com/k9lzb SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Could have come from John Walkenbach, too. > [quoted text clipped - 89 lines] > > > > > > Dave Peterson I believe you. I just don't remember it. (And I still say that I got it from Peo!) > It seems no one wants to believe me when I say that it came from Leo Heuser, > way back in 2001! [quoted text clipped - 111 lines] > > > > Dave Peterson SignatureDave Peterson Oops - I forgot to mention, I also need to isolate the "other" part of the name: Joe Joe E. Joe Edward Smith, I can't really decipher your formula enough to extrapolate the reverse of this. Thanks > =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1) > -LEN(SUBSTITUTE(A1," ",""))))) [quoted text clipped - 20 lines] > > > > THANK YOU Actually, I figured it out. I just took the length of the last word, then use LEFT function... thanks tootsu...@gmail.com wrote: > Oops - I forgot to mention, I also need to isolate the "other" part of > the name: [quoted text clipped - 32 lines] > > > > > > THANK YOU BRILLIANT!! (Both of you) Dave, could you please explain the use of "^^"? > Actually, I figured it out. I just took the length of the last word, > then use LEFT function... thanks [quoted text clipped - 36 lines] > > > > > > > > THANK YOU I believe it is just a substitute character for the space - so that the the last space, the one before the last word, is ^^, not " " - so as to differentiate it from just a plain " " me thinks anyways > BRILLIANT!! (Both of you) > [quoted text clipped - 40 lines] > > > > > > > > > > THANK YOU Yeah, I think you are right - you are not likely to get that character combination in any normal text. Dave often uses $$ in Find and Replace operations, so I suppose if he hadn't "lifted" if from Peo, then you might have found that combination of characters in the formula. <g> Pete > I believe it is just a substitute character for the space - so that the > the last space, the one before the last word, is ^^, not " " - so as to [quoted text clipped - 46 lines] > > > > > > > > > > > > THANK YOU I get it now. Thanks again. > Yeah, I think you are right - you are not likely to get that character > combination in any normal text. Dave often uses $$ in Find and Replace [quoted text clipped - 53 lines] > > > > > > > > > > > > > > THANK YOU Actually, I think I would have used char(1). The ^^ could show up in a cell. But I don't recall seeing anyone use char(1) in any worksheet. > Yeah, I think you are right - you are not likely to get that character > combination in any normal text. Dave often uses $$ in Find and Replace [quoted text clipped - 57 lines] > > > > > > > > > > > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.