Extracting All But Last Word

  • Thread starter Thread starter Steve Madden
  • Start date Start date
S

Steve Madden

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top