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

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

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