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
0
4/13/2004 7:16:56 PM
excel 39879 articles. 2 followers. Follow

4 Replies
673 Views

Similar Articles

[PageSpeed] 41

can you upload example, would be easier to view

--
Message posted from http://www.ExcelForum.com

0
4/13/2004 7:35:10 PM
You can search for the space at the end of the word, and replace to that 
point:

=REPLACE(I2,SEARCH(E2,I2),
   SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),".....")

Runt wrote:
> 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"?

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/13/2004 9:25:21 PM
The solution you gave...

> =REPLACE(I2,SEARCH(E2,I2),
>    SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),"....."

....is excellent except that it doesn't replace the word if it is at
the end of the sentence because of the lack of a space or the
existance of a full-stop (a.k.a period).

Is there any way around this?

Thanks,

Chris



Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<407C5AC1.9000601@contexturesXSPAM.com>...
> You can search for the space at the end of the word, and replace to that 
> point:
> 
> =REPLACE(I2,SEARCH(E2,I2),
>    SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),".....")
> 
> Runt wrote:
> > 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"?
0
4/14/2004 8:52:34 AM
I seem to have solve the end-of-sentence problem  by substituting the
punctuation for spaces as follows:

=REPLACE(I2,SEARCH(E2,I2), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(I2,".","
"),"?"," "),SEARCH(E2,I2))-SEARCH(E2,I2),".....")

But, I've stumbled across a complication which has stretched my tiny
brain to its limit.

If the text in cell (E2) has a space in it, such as "TRYING TO" and I
want to remove it from the sentence "I am TRYING TO remove this.",
then I am getting

"I ..... TO remove this." instead of

"I ..... remove this." which is what I want.

Any ideas?

Cheers,

Chris


Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<407C5AC1.9000601@contexturesXSPAM.com>...
> You can search for the space at the end of the word, and replace to that 
> point:
> 
> =REPLACE(I2,SEARCH(E2,I2),
>    SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),".....")
> 
> Runt wrote:
> > 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"?
0
4/14/2004 9:25:06 AM
Reply:

Similar Artilces:

I cannot follow links in Word 2007
I have just upgraded to Office 2007. I am enrolled online in a couple of classes. One of my classes, requires me to download Word Documents, and do assigments. In one of the documents, there are Hyperlinks to websites. Everytime I try to follow the hyperlink, I get an error message that says: "This Operation has been canceled, due to restrictions on this computer. Pleas Contatc your system administrator." I am my system's administrator, and have no idea why this is now working. Please Help. Hi Jbulger, Your PC's firewall may be causing this behaviour. -- ...

When Word Files are Opened, They
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Since installing the latest upgrade for Microsoft Word 2008 for Mac (Version 12.2.3 (091001)) I have noticed that when I have one or more files open, and I open a new file, the new file doesn't appear in the foreground. When I go the the Window menu, however, I see that the new file is in the list, and I can select the new file. <br><br>Has anyone else noticed this bug? Hi Mary: You have not stated your update levels so the only thing we can say is "Make sure you have applied all the ...

Histogram Function has a problem
When I create a histogram in the 2007 Beta, I get not just my data graphed, but also the bin range along with it. How do I get EXCEL to stop doing this automatically? Use 2003? You shouldn't expect something called a "Beta" to be ready for real work. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Boopsie" <Boopsie@discussions.microsoft.com> wrote in message news:662F0691-7188-4DAA-8BE5-8E75C2C3ECD6@microsoft.com... > When I create a histogram in the 2007 Beta, I get not just my data >...

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

Outlook closes Word after each message
For some reason, Outlook closes Word after each message, and then re-installs it for the following message...and also duplicates a lot of the messages (but not always). I tried to repair Office pro, but got the messge; "this patch package could not be opened. Verify that the patch package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer patch package" I also tried to uninstall and reinstall Office Pro, but got the same message as above, an it would not let me uninstall the program. ...

How do I add accents to Spanish words?
I am trying to add accents to Spanish words. Any suggestions. Thanks Hi, Use AltGr+the accent (~), release AltGr, press n and you get � Regards Leif "syssupspe" <syssupspe@discussions.microsoft.com> wrote in message news:707F012F-3607-4F01-AAE8-999EC22B06B5@microsoft.com... >I am trying to add accents to Spanish words. Any suggestions. Thanks Leif What is AltGr? Thanks, Gord Dibben Excel MVP On Tue, 14 Sep 2004 14:01:19 GMT, "Leif" <leifhy@hotmail.com> wrote: >Hi, >Use AltGr+the accent (~), release AltGr, press n and you get � >Regards &...

Hyperlink function presents variable results does not work in macro
I need some assistance if possible using a hyperlink function within a macro The hyperlink function is presenting the current address in excel (address varies) but there is no change within the macro code It appears that the macro once recorded cannot be "updated" to the address that is current. Is there a way around, through or over this. I am using the hyperlink to select a range which can then be "updated" as part of the macro Thanks for any assistance -- Message posted via http://www.officekb.com As ALWAYS, post your coding efforts for comments. --...

Function/Formatting
Have a spreadsheet with a range of numbers(for ref the last 30 lotter draws) in cells A1:g31. What I would like is - if a number is entere in say cell m2, a formula or conditional format that would highligh that number in the range. Is it possible? Thanking everyone in advance. Scap ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way, select A1:G31 with A1 as the active cell (if you start selecting from A1 it will be active) or select it by typing A1:A31 in the n...

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

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

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

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

How do I check error result occurring by filecopy function in VBA
I writing in VBA for excell. My program calls the filecopy function in a private function. If filecopy fails, the program should analyze it and return with a failure value to the main subrotine that calld the fuction (in a success execution of filecopy, the function returns the workbook object of the new destination file). How can I check the status of the execution? How can I found out if the returnd workbook object has value or it's empty? Thanks Hi I tend to do something like Err.clear 'err is not destroyed when the sub ends On Error Resume Next 'do the co...

store acronyms in the word dictionary so they are not spell-checke
I often prepare government documents and as a rule acronyms are part of the language... E.g., DoD is one that is used all of the time. The question is how to stop Word (2003) from always flagging this acronym as a spelling error. I've tried saving the acronym in the dictionary but this has not helped. Moreover, Word always tries to capitalize DoD to: DOD... and this is not what is required. I think the problem is that Word is not seeing this as a spelling error at all. I think it's seeing it as a capitalization error. Check your AutoCorrect options and see if one of...

Problems with the GetFileSecurity function
Hello, I have the following problem. I need to obtain current user's rights for the speciefied file or directory (read, write, delete, etc.) and the file owner name. I've found out that it can be made by using the GetFileSecurity function. But I'm not familiar with all these ACLs, DACLs, etc. So I will appreciate if anyone can help me with some code example. Thanks. Following articles: http://www.codeproject.com/win32/accessctrl1.asp (The Windows Access Control Model Part N) will help you. -- Vladimir "cozzzy" <cozy_dude@mail.ru> wrote in message news:11390...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Excel object in Word
How do I get rid of the gride lines in an embedded excel object in word? Gridlines are off in Print Setupand Drawing menus. Jon In the same way you'd do it in an Excel worksheet itself? Try this: Double-click on the embedded Excel object in Word (Or right-click on it > Worksheet object > Edit) Click Tools > Options > View tab Uncheck "Gridlines" > OK Click anywhere outside the object to get back to Word -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "Jon Ford" wrote: >How do I get rid of the ...

automatic and undesired logout of Word
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I leave Word running, the computer automatically logs me out after a while. If the document has not been saved, I usually get a message saying that I could not be logout because.... (and then some reason). But if all the documents have been saved, then the machine just logs me out and closes down the documents I left open and Word itself. <br><br>As I said, it happens when the computer goes to screen saver mode because it's been inactive for a while. <br><br>Why is this happen...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Export WPX4 Address book to MS Word 2007?
Is it possible to transfer my WordPerfect X4 address book to Word 2007 for use in addressing envelopes etc.? I have the home and school version of 07. Thanks, Rich Mercer Word doesn't have an address book. It utilises the contacts list of Outlook, which of course the student version of Office does not include. It *may* be possible to extract the data from your WP address book to a Word table to use as a data source for mail merge, but I suspect you may have to do this from Word Perfect. If you still have access to WP, check if it has an option to export to a comma delim...

.eps problems in publisher and word
I have created an .eps file using Adobe IllustratorCS2 with a .tiff preview. I have installed the filter for Microsoft office 2003 to read the .eps files but I still get a blank box. There is nothing in the box, no text or image. What else can I do. At one time, I could see my files when I used an older version of Illustrator. I could convert to other raster formats but they become grainy when enlarged. I would rather use .eps for vector type art. Help, this is very frustrating to create a piece and then not be able to place it in an office program. Description of the Office 2003 hotf...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...