| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Address Formula
One way:
=MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Steve M wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Address Formula
Try this:
=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) "Steve M" wrote: 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Address Formula
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). "Dave Peterson" wrote in message ... One way: =MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Steve M wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Address Formula
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 Steve M wrote: 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). "Dave Peterson" wrote in message ... One way: =MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Steve M wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Address Formula
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) vezerid wrote: 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 Steve M wrote: 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). "Dave Peterson" wrote in message ... One way: =MID(A1,1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1) -LEN(SUBSTITUTE(A1," ",""))))) Steve M wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is it possible? | Excel Worksheet Functions | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| How do I use cell contents as an address in a formula | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Help! Formula to find the address of particular value in sheet | Excel Worksheet Functions | |||
Linear Mode
