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

[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!

[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.

[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.

--
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!

[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

--
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!

[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.

"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
>
>

[Peo Sjoblom]

Try

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


--

Regards,

Peo Sjoblom

"George" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

[Peo Sjoblom]

Try

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


--

Regards,

Peo Sjoblom

"George" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

[George]

That is perfect! Thank you very much.

"Peo Sjoblom" wrote:

> Try
>
> =SUBSTITUTE(A1," "," %",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "George" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
>
>
>