How do I replace the last entire word in text cells?

G

George

I understand the REPLACE command but it seems restricted to characters or
bytes. I this case, I want to replace the last word in all cells but the word
is always different (ie. varying number of characters).

Thanks in advance!
 
D

David Billigmeier

Array entered (CTRL+SHIFT+ENTER), this will work:

=LEFT(A1,LEN(A1)-MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0))

Change the reference to A1 to which ever cell you want.
 
D

David Billigmeier

Sorry... one little modification assuming the word you want to append to the
end is located in B1, add the "&B1" to the end (my last post will just strip
off the last word), still array entered (CTRL+SHIFT+ENTER):

=LEFT(A1,LEN(A1)-MATCH("
",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0))&B1
 
G

George

That is very helpful but I should have been more clear.
What I really need to do is add a character (ie. %) to the begining of the
last word.

Thanks for your help.
 
V

Vito

Assuming spaces between words, try

=SUBSTITUTE(A1,(RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"
","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))),B1)

Where A1 contains the phrase and B1 contains the new word to insert.

You can also replace teh B1 in the formula with the actual word in
double quotes, if desired.
 
Top