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!
0
George6668 (260)
12/15/2005 7:18:03 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
1121 Views

Similar Articles

[PageSpeed] 24

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!
0
12/15/2005 7:29:03 PM
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!
0
12/15/2005 7:36:04 PM
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?action=getinfo&userid=29182
> View this thread: http://www.excelforum.com/showthread.php?threadid=493901
> 
> 
0
George6668 (260)
12/15/2005 8:00:03 PM
Try

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


-- 

Regards,

Peo Sjoblom

"George" <George@discussions.microsoft.com> wrote in message
news:119B5BDC-8309-4A4A-B2A7-6CB775E7C9AF@microsoft.com...
> 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?action=getinfo&userid=29182
> > View this thread:
http://www.excelforum.com/showthread.php?threadid=493901
> >
> >


0
terre081 (3244)
12/15/2005 8:22:44 PM
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?action=getinfo&userid=29182
View this thread: http://www.excelforum.com/showthread.php?threadid=493901

0
12/15/2005 8:28:20 PM
That is perfect! Thank you very much.

"Peo Sjoblom" wrote:

> Try
> 
> =SUBSTITUTE(A1," "," %",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> "George" <George@discussions.microsoft.com> wrote in message
> news:119B5BDC-8309-4A4A-B2A7-6CB775E7C9AF@microsoft.com...
> > 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?action=getinfo&userid=29182
> > > View this thread:
> http://www.excelforum.com/showthread.php?threadid=493901
> > >
> > >
> 
> 
> 
0
George6668 (260)
12/15/2005 8:30:02 PM
Reply:

Similar Artilces:

Delete blank Last page
In a word document where the last page is blank due to editing or whatnot, how do you get rid of the frigg' thing. This is a Visio newsgroup - your question was about Word. However, I have had this problem as you have. Narrowing the margins and line spacing on the page previous to the last may get rid of this annoying "blank" page for you. - Jeff Chapman "silkies" <silkies@discussions.microsoft.com> wrote in message news:BDCC1FF1-50D0-42CE-BA84-868CB26A43AC@microsoft.com... > In a word document where the last page is blank due to editing or > whatnot...

original text spell checked
running exchange 2000\sp3 with outlook 2000\sp2 clients. Mail format set to html, use word to edit html and rich text messages is checked. along with the option to ignore original message text in reply or forward on the spelling tab. We are still having the original text spell checked even though our options are set to not do this. Am I missing something? thanks, ...

Cell Colors
Tom, How would I check to see if it was turned off? And if so, how would I turn it on? Thanks Bill Bill, The article says 1.. Click Start, click Control Panel, and then click Accessibility Options. 2.. Click the Display tab, and then click to clear the Use High Contrast check box. 3.. Click OK to close the Accessibility Options dialog box. so I guess you can check by going to that Display tab and see if the Use High Contrast checkbox is set. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) &q...

Quick Campaigns & Last Date Included in Campaign
Hi colleagues Do you know what about Last Date Included in Campaign (Customer Attribute) and Quick Campaigns? It seems that QCampaigns do nothing with this field. Is it correct? Is it a bug? Kind Regards ...

Center text in text box
I produced our club's latest newsletter on Publisher for the first time and overall was a pretty pain free experience. Very intuitive program. I was unable to find a command to center text within a text box. Is this feature not present in Publisher as it is in word? It was not on the tool bar with the margin justifying commands and my quick search didn't bring it up. I ended up ball parking my lines in the center with tab and space bar. Any suggestions? Right-click the toolbar, tick "formatting". -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Offset text print problems
recently all of my excel documents are printing the text offset from the gridlines. I have checked through every setting I can think of and am having no luck. I have even changed the font I am using. Any ideas on why this is happening and or how to fix it ? I have no idea, but I'd start with the printer driver. Either get a fresh or new driver from your printer's manufacturer. You may even want to try it on a different printer to see if that makes a difference. IMC Techie wrote: > > recently all of my excel documents are printing the text offset from the > gridlines....

Links into blank cells
I have a formula which sums 2 or more cells in a separate worksheet. However if any of these cells are blank the formula returns a '#VALUE'. Bizarrely the same formula into a different worksheet does not cause this error message. Any idea's other than a long IFISERROR formula? The cells in the separate worksheets may contain a blank space, as if someone typed just the space bar and pressed return. That cell would look blank, visually, but Excel would evaluate it as a text entry and return the VALUE error because Excel doesn't perform math on a text string (without some ext...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

how do you "select locked cells" w/o "select unlocked cells"?
I am using Excel 2003. I am attempting to protect only certain cells within an Excel worksheet. I first click on FORMAT, CELLS, and "deselect" the "LOCKED" check box. I then highlight the cells I wish to protect, repeat that step, only this time make sure the LOCKED check box is checked. I then go to to TOOLS, PROTECTION, PROTECT SHEET, and attempt to select the option, and JUST the option for "Select locked cells", however, everytime I select this option, a checkmark appears next to "Select unlocked cells" as well. One seems to trigger the othe...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Access 2002/2003 VBA What kind of file is .CRC (Binary, text) ? How do I read it with FreeFile()?
Dear All Access 2002 w/SP3 Access 2003 w/SP2 Windows XP Pro w/SP2 Type of file: CRC File Opens with: Unknown application I want to read all the data in a .CRC file I know I can read it using FSO, if I click on File name -> right click on properties, Changes and choose the notepad to open it. But I do not want to do this, I want to open the file as is using FreeFile() and read all the data The file was created using a program called CdCheck http://www.kvipu.com/CDCheck/ Can someone please help? Thanks in advance Regards, Antonio Macias rebelscum0000 <piiixp@jedikeeper.com> wro...

Align cell contents on left side of sheet?
I want to align all cells to the left side of the sheet. Example, some rows have entries in the middle of the speedsheet with no enteries in the beginning. I am sure it has to due with the formating, just don't know how to do it. Any help would be greatly appreciated. Thanks Nothing to do with formatting. If there is no value in a cell, that cell will be blank. You could select all then F5>Special>Blanks>OK Edit>Delete>Shift cells left. But that could mess things up so do it on a copy of the original sheet. Gord Dibben MS Excel MVP On Mon, 6 Aug 2007 10:16:02...

Word Mail Merge with Excel Data--Missing Data
I have created a mail merge document in Word2002, using Excel2002 for m data source. When I merge the document, some of my fields hav incomplete data. The only records affected are those where the dat from the Excel fields is fairly long. Only about 200 characters wil show up on the Word doc. I am not exceeding any limits in my Exce fields. I've tried everything I know to do, but can't get this one t budge. Any suggestions are welcome -- Message posted from http://www.ExcelForum.com The following posts by Word MVP, Cindy Meister, may help you: http://groups.google.com/grou...

Vista (copy Excel to Word)
Hi, I'm trying to copy (make as an icon) an excel document into a word document. *Steps I've tried (In word):* 1. Hit insert tab 2. Click on object button, then chose object 3. Click on Create from File tab 4. Hit Browse button, then I chose the excel document I wanted copied. 5. I have then tried Display as Icon Didn't work. *I received error*: The program used to create this object is Excel. That program i snot installed on your computer. To edit this object, you must install a program that can open the object. I then tried not clicking Display as Icon, but s...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Cell Editing
How to you set the program to allow cell editing followed directly by using a cursor key instead of ENTER (like Lotus....) Hit F2 to enter edit mode(see Edit on Status bar). Arrows will move within the cell. Hit F2 again to enter "Enter" mode(see Enter on Status bar). Arrows will take you out of the cell. Gord Dibben Excel MVP On Fri, 11 Feb 2005 08:57:05 -0800, "remco2mill" <remco2mill@discussions.microsoft.com> wrote: >How to you set the program to allow cell editing followed directly by using a >cursor key instead of ENTER (like Lotus....) Look at...

Add the last value as a label
I want to add the last value of the "Y" on the graph. The last value's row location varies. So I need something to read it and label it in the graph automatically because I have been doing it manually and it is a lot time consuming because I have several graph to update per date. Thanks. Maperalia. Is it an XY chart? Add a series with a single point and a data label. Assume your data consists of X values in A4:A(whatever), and Y values in column B next to the X values, with labels in A3 and B3, with no blank cells or rows. Put new labels in C3 and D3 for the new point, I...

AutoCorrect
In my AutoCorrect window next to Replace and With, why are the options plain text and formatted text greyed out? "Formatted text" is available only if you have selected formatted text before opening the dialog (or pasted in text that has characters that require formatting). "Formatted text" means any text that contains characters from more than one font or that has, say, italic or bold formatting applied. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Elizabeth" <Elizabeth@discus...

Is the text implying that FORMSOF(INFLECTIONAL...) will match either nouns or verbs, but not both?
hi 1) > A given <simple_term> within a <generation_term> will not match both noun= s and > verbs. If I understand the above text correctly, then query SELECT * FROM someTable WHERE CONTAINS ( *, ' FORMSOF ( INFLECTIONAL, park ) ' ) will search for either nouns or verbs derived from the root word =93park=94, but not for both? Thus out of the two rows, one containing noun =93parks=94 and other verb =93parking=94, the above query should retur= n just one of the two rows? But as it turns out, query returns both rows, so perhaps my assumptions are a ...

if both cells are same highlight
hi there, i need a formula for conditional formatting to highlight the cell if it is the same as the other cell. example below. I would like column B cells to highlight if they are the same as A. A B 120 100 150 120 100 100 Thank you for your help in advance. v Hi, Put this conditional format in b1 and pick a colour. Use the format painter to copy it down column B =AND(A1<>"",A1=B1) Mike "veena" wrote: > hi there, i need a formula for conditional formatting to highlight the cell ...

What function to select the last 3 smallest integers?
Does it have a single function to perform the similar task? Given Lists {1,2,3,4,5,6,7,8,9} Given number {2,3,7} Condition: If any given number equals to any last 3 small integers, such as {1,2,3} in this case, then TRUE. 2 is TRUE 3 is TRUE 7 is FALSE Does anyone have any idea? Thank you Eric =ISNUMBER(MATCH(3,SMALL(A1:A9,{1,2,3}),0)) where A1:A9 holds the list of numbers -- HTH RP (remove nothere from the email address if mailing direct) "Eric" <Eric@discussions.microsoft.com> wrote in message news:4F9D4C71-4BBB-4585-984E-7B29DFFF85F2@microsoft.com... > Does it ...