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

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!

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

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.

--
Regards,
Dave


"George" wrote:

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!

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

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

--
Regards,
Dave


"David Billigmeier" wrote:

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.

--
Regards,
Dave


"George" wrote:

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!

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

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.

"Vito" wrote:


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.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=493901

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

Try

=SUBSTITUTE(A1," "," %",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))


--

Regards,

Peo Sjoblom

"George" wrote in message
...
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.

"Vito" wrote:


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.


--
Vito
------------------------------------------------------------------------
Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
View this thread:
http://www.excelforum.com/showthread...hreadid=493901

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

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.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=493901

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

That is perfect! Thank you very much.

"Peo Sjoblom" wrote:

Try

=SUBSTITUTE(A1," "," %",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))


--

Regards,

Peo Sjoblom

"George" wrote in message
...
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.

"Vito" wrote:


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.


--
Vito
------------------------------------------------------------------------
Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
View this thread:
http://www.excelforum.com/showthread...hreadid=493901