| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing words/phrase from sentences
I'm looking to create a column of sentences where the words/phrases
which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ....then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ....but it gives... Cell C1 = "They plan on ..... over." ....which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing words/phrase from sentences
Hi
don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing words/phrase from sentences
Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature
in Excel as in MS Word? Could this not be used to capture changing tenses? What about matching the first 3 or 4 letters of the word. Would this capture the majority of cases where the tense was different? Thanks again for help. Chris "Frank Kabel" wrote in message ... Hi don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
removing words/phrase from sentences
Hi
there's no MatchAllWordForm feature in Excel. Regarding your other question: yes it probably will -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag m... Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature in Excel as in MS Word? Could this not be used to capture changing tenses? What about matching the first 3 or 4 letters of the word. Would this capture the majority of cases where the tense was different? Thanks again for help. Chris "Frank Kabel" wrote in message ... Hi don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing *words from sentences
If I have a field which contains sentences and some of the words in a
sentence has an asterix beside them, e.g. "*Mary had *a little *Lamb" ....would it be possible to write a function that would produce a sentence where the asterixed words are replace with a gap ("....."), e.g. "..... had ..... little ....." If so, I'd really appreciate a pointer to how I might achieve this. Thanks in advance for your time. Chris "Frank Kabel" wrote in message ... Hi there's no MatchAllWordForm feature in Excel. Regarding your other question: yes it probably will -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag m... Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature in Excel as in MS Word? Could this not be used to capture changing tenses? What about matching the first 3 or 4 letters of the word. Would this capture the majority of cases where the tense was different? Thanks again for help. Chris "Frank Kabel" wrote in message ... Hi don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing *words from sentences
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing *words from sentences
I'd use a user defined function for this.
If that's ok: Option Explicit Function ReplaceStarWords(rng As Range, _ Optional WithString As String = ".....") As String Dim StarPos As Long Dim SpacePos As Long Dim myStr As String myStr = rng(1).Value Do StarPos = InStr(1, myStr, "*", vbTextCompare) If StarPos = 0 Then Exit Do End If SpacePos = InStr(StarPos, myStr, " ", vbTextCompare) If SpacePos = 0 Then SpacePos = Len(myStr) + 1 End If myStr = Application.Substitute(myStr, _ Mid(myStr, StarPos, SpacePos - StarPos), WithString) Loop ReplaceStarWords = myStr End Function Then you could use that formula in a helper cell. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ======= Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =ReplaceStarWords(a1) You could even pass it a different string to use: =replacestarwords(a1,"???") if you want. If you want to update the values in place, I'd use this little macro to change all the Selected cells. Sub testme() Dim myCell As Range For Each myCell In Selection.Cells myCell.Value = ReplaceStarWords(myCell) Next myCell End Sub Runt wrote: If I have a field which contains sentences and some of the words in a sentence has an asterix beside them, e.g. "*Mary had *a little *Lamb" ...would it be possible to write a function that would produce a sentence where the asterixed words are replace with a gap ("....."), e.g. "..... had ..... little ....." If so, I'd really appreciate a pointer to how I might achieve this. Thanks in advance for your time. Chris "Frank Kabel" wrote in message ... Hi there's no MatchAllWordForm feature in Excel. Regarding your other question: yes it probably will -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag m... Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature in Excel as in MS Word? Could this not be used to capture changing tenses? What about matching the first 3 or 4 letters of the word. Would this capture the majority of cases where the tense was different? Thanks again for help. Chris "Frank Kabel" wrote in message ... Hi don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing *words from sentences
Dave,
It works! Thank you. You've been a great help. Cheers, Chris Dave Peterson wrote in message ... I'd use a user defined function for this. If that's ok: Option Explicit Function ReplaceStarWords(rng As Range, _ Optional WithString As String = ".....") As String Dim StarPos As Long Dim SpacePos As Long Dim myStr As String myStr = rng(1).Value Do StarPos = InStr(1, myStr, "*", vbTextCompare) If StarPos = 0 Then Exit Do End If SpacePos = InStr(StarPos, myStr, " ", vbTextCompare) If SpacePos = 0 Then SpacePos = Len(myStr) + 1 End If myStr = Application.Substitute(myStr, _ Mid(myStr, StarPos, SpacePos - StarPos), WithString) Loop ReplaceStarWords = myStr End Function Then you could use that formula in a helper cell. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ======= Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =ReplaceStarWords(a1) You could even pass it a different string to use: =replacestarwords(a1,"???") if you want. If you want to update the values in place, I'd use this little macro to change all the Selected cells. Sub testme() Dim myCell As Range For Each myCell In Selection.Cells myCell.Value = ReplaceStarWords(myCell) Next myCell End Sub Runt wrote: If I have a field which contains sentences and some of the words in a sentence has an asterix beside them, e.g. "*Mary had *a little *Lamb" ...would it be possible to write a function that would produce a sentence where the asterixed words are replace with a gap ("....."), e.g. "..... had ..... little ....." If so, I'd really appreciate a pointer to how I might achieve this. Thanks in advance for your time. Chris "Frank Kabel" wrote in message ... Hi there's no MatchAllWordForm feature in Excel. Regarding your other question: yes it probably will -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag m... Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature in Excel as in MS Word? Could this not be used to capture changing tenses? What about matching the first 3 or 4 letters of the word. Would this capture the majority of cases where the tense was different? Thanks again for help. Chris "Frank Kabel" wrote in message ... Hi don't think this is really possible in Excel. Esp. with changing tense, etc. -- Regards Frank Kabel Frankfurt, Germany "Runt" schrieb im Newsbeitrag om... I'm looking to create a column of sentences where the words/phrases which appear in Column A have been removed from the sentences which appear in Column B so if... Cell A1 = "take sg over" Cell B1 = "They plan on taking the company over." ...then I want to get... Cell C1 = "They are planning to ...... the company ......" Do you think it is possible to do this using either a MACRO or a FUNCTION?. I'm currently using the following function for Cell C1... =REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!"," "),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....") ...but it gives... Cell C1 = "They plan on ..... over." ...which is obviously inadequate. Any ideas? Your help would be greatly appreciated. Chris |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Split several sentences in one cell | Excel Discussion (Misc queries) | |||
| Split sentences into words | Excel Discussion (Misc queries) | |||
| removing a space between words in a cell | Excel Worksheet Functions | |||
| I need to put 4-5 sentences into one cell. | Excel Worksheet Functions | |||
| Removing rows featuring certain words | Excel Programming | |||
Linear Mode
