 |
 | 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 / Worksheet Functions / November 2006Help with Address Formula
I am looking for a formula that will return all words except for the last word. Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in cell A2: Castleton Parkway TIA One way: =MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) > I am looking for a formula that will return all words except for the last > word. > Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in > cell A2: Castleton Parkway > > TIA  SignatureDave Peterson When I use this formula it returns Cast. I might mention also that there would usually be a number in front of the street address (e.g. 125 Castleton Parkway Blvd--in such a case I would want the result of the formula to be 125 Castleton Parkway). > One way: > [quoted text clipped - 8 lines] >> >> TIA Just a minor modification of Dave's formula: =MID(A1,1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) HTH Kostis Vezerides > When I use this formula it returns Cast. I might mention also that there > would usually be a number in front of the street address (e.g. 125 Castleton [quoted text clipped - 12 lines] > >> > >> TIA Thanks for the correction... And just because of the unfortunate line wrap: =MID(A1,1,FIND(CHAR(1), SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) > Just a minor modification of Dave's formula: > [quoted text clipped - 24 lines] > > > > > > Dave Peterson SignatureDave Peterson Try this: =LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) > I am looking for a formula that will return all words except for the last > word. > Eample:. in cell A1 I have: Castleton Parkway Blvd--I want to return in > cell A2: Castleton Parkway > > TIA |
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
|
©2008 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.