| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting All But Last Word
I work with street addresses and want to make changes to the suffixes
without changing the rest of the address. I know how to use a function to copy the suffix (or last word in a cell) to another cell. But, I do not know how to copy everything EXCEPT the suffix (or last word in a cell) to another cell. Please suggest a formula that will do this. TIA |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting All But Last Word
Try this:
For text in A1 B1: =LEFT(A1,LEN(A1)-MATCH(" ",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula pulls text to the left of the last space-character in the string. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Steve Madden" wrote: I work with street addresses and want to make changes to the suffixes without changing the rest of the address. I know how to use a function to copy the suffix (or last word in a cell) to another cell. But, I do not know how to copy everything EXCEPT the suffix (or last word in a cell) to another cell. Please suggest a formula that will do this. TIA |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Extracting All But Last Word
This method (posted by Domenic some time ago) uses a non-array formula:
=LEFT(A1,SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1) Just press [Enter] to commit that formula. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try this: For text in A1 B1: =LEFT(A1,LEN(A1)-MATCH(" ",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula pulls text to the left of the last space-character in the string. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Steve Madden" wrote: I work with street addresses and want to make changes to the suffixes without changing the rest of the address. I know how to use a function to copy the suffix (or last word in a cell) to another cell. But, I do not know how to copy everything EXCEPT the suffix (or last word in a cell) to another cell. Please suggest a formula that will do this. TIA |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking table in Excel to word | Links and Linking in Excel | |||
| Print Excel charts in Word 2003 with fixed size | Charts and Charting in Excel | |||
| Extracting info from word and displaying in an excel spreadsheet | Excel Discussion (Misc queries) | |||
| How to embed Word document into Excel and retain sizing, formatti. | Excel Discussion (Misc queries) | |||
| Embedded word doc changed to image-need to change back to word. | Excel Discussion (Misc queries) | |||
Linear Mode
