| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) | |||
| Split Long Text Cell into Two Shorter Cells Without Splitting Word | Excel Discussion (Misc queries) | |||
| Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Linear Mode
