|
Extracting word from phrase within column
Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
You could use a regexp to do this. Search for help on the VBscript RegExp
object. Or just split on spaces and check each word to see if it's like "*d" -- Tim Williams Palo Alto, CA "KH_GS" wrote in message ... Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
wrote: Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? Give some examples of cell contents and expected output. Multiple words in each source cell or a single word per cell? Also what kind of data size do you have (how many characters per cell; how many cells, on average)? --ron |
Extracting word from phrase within column
I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. Ron Rosenfeld Wrote: On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS wrote: Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? Give some examples of cell contents and expected output. Multiple words in each source cell or a single word per cell? Also what kind of data size do you have (how many characters per cell; how many cells, on average)? --ron -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS
wrote: I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. You can use the VBA Regular expressions module, but I have loaded and installed Longre's free morefunc.xll add-in which is simpler for me to implement. It can be distributed with any workbook, so you don't have to rely on users to install it separately. You can download it from http://xcell05.free.fr If you don't install the addin (Tools/Addins) then you'll have to register it to use it in VBA. See HELP for morefunc for instructions. A VBA routine like the following will do what you describe. Should give you some ideas to get started: ======================== Option Explicit Sub EndWithD() Dim c As Range Dim output As Range Dim wrd As String Dim i As Long, o As Long Set output = [b1] o = -1 For Each c In Selection i = 1 Do Until i Run([REGEX.COUNT], c.Text, "\b\w+d\b") wrd = Run([regex.mid], c.Text, "\b\w+d\b", i) If wrd < "" Then o = o + 1 output.Offset(o, 0).Value = wrd End If i = i + 1 Loop Next c End Sub ==================== --ron |
Extracting word from phrase within column
What if a cell has 1 D word?
Tim "KH_GS" wrote in message ... I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. Ron Rosenfeld Wrote: On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS wrote: Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? Give some examples of cell contents and expected output. Multiple words in each source cell or a single word per cell? Also what kind of data size do you have (how many characters per cell; how many cells, on average)? --ron -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
On Tue, 28 Mar 2006 23:22:15 -0500, Ron Rosenfeld
wrote: On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS wrote: I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. You can use the VBA Regular expressions module, but I have loaded and installed Longre's free morefunc.xll add-in which is simpler for me to implement. It can be distributed with any workbook, so you don't have to rely on users to install it separately. You can download it from http://xcell05.free.fr If you don't install the addin (Tools/Addins) then you'll have to register it to use it in VBA. See HELP for morefunc for instructions. A VBA routine like the following will do what you describe. Should give you some ideas to get started: ======================== Option Explicit Sub EndWithD() Dim c As Range Dim output As Range Dim wrd As String Dim i As Long, o As Long Set output = [b1] o = -1 For Each c In Selection i = 1 Do Until i Run([REGEX.COUNT], c.Text, "\b\w+d\b") wrd = Run([regex.mid], c.Text, "\b\w+d\b", i) If wrd < "" Then o = o + 1 output.Offset(o, 0).Value = wrd End If i = i + 1 Loop Next c End Sub ==================== --ron Hmmm, the IF...Then is superfluous. It was there in a preliminary version, but not required, so should be: ============================== Option Explicit Sub EndWithD() Dim c As Range Dim output As Range Dim wrd As String Dim i As Long, o As Long Set output = [b1] o = -1 For Each c In Selection i = 1 Do Until i Run([REGEX.COUNT], c.Text, "\b\w+d\b") wrd = Run([regex.mid], c.Text, "\b\w+d\b", i) o = o + 1 output.Offset(o, 0).Value = wrd i = i + 1 Loop Next c End Sub ======================== --ron |
Extracting word from phrase within column
On Tue, 28 Mar 2006 20:33:32 -0800, "Tim Williams" <timjwilliams at gmail dot
com wrote: What if a cell has 1 D word? Tim No problem. That's the reason for the REGEX.COUNT function. Note that the 'i' argument in the REGEX.MID function is for the instance of the occurrence. Note also that the If...Then in the middle is superfluous. It was present in an earlier version, but checking the number of words with the COUNT function eliminated the requirement to test the output; as the entire cell will be skipped if there is no D word. --ron |
Extracting word from phrase within column
On Tue, 28 Mar 2006 23:22:15 -0500, Ron Rosenfeld
wrote: On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS wrote: I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. You can use the VBA Regular expressions module, but I have loaded and installed Longre's free morefunc.xll add-in which is simpler for me to implement. It can be distributed with any workbook, so you don't have to rely on users to install it separately. You can download it from http://xcell05.free.fr If you don't install the addin (Tools/Addins) then you'll have to register it to use it in VBA. See HELP for morefunc for instructions. A VBA routine like the following will do what you describe. Should give you some ideas to get started: ======================== Option Explicit Sub EndWithD() Dim c As Range Dim output As Range Dim wrd As String Dim i As Long, o As Long Set output = [b1] o = -1 For Each c In Selection i = 1 Do Until i Run([REGEX.COUNT], c.Text, "\b\w+d\b") wrd = Run([regex.mid], c.Text, "\b\w+d\b", i) If wrd < "" Then o = o + 1 output.Offset(o, 0).Value = wrd End If i = i + 1 Loop Next c End Sub ==================== --ron Hmmm, the IF...Then is superfluous. It was there in a preliminary version, but not required, so should be: ============================== Option Explicit Sub EndWithD() Dim c As Range Dim output As Range Dim wrd As String Dim i As Long, o As Long Set output = [b1] o = -1 For Each c In Selection i = 1 Do Until i Run([REGEX.COUNT], c.Text, "\b\w+d\b") wrd = Run([regex.mid], c.Text, "\b\w+d\b", i) o = o + 1 output.Offset(o, 0).Value = wrd i = i + 1 Loop Next c End Sub ======================== One other warning: the routine above is case sensitive. If you want it to be case insensitive, that's a minor change. --ron |
Extracting word from phrase within column
Sorry Ron - my question was for the OP. It was unclear how this was to be
handled. I was a little behind you with my regex post: yours is much more concise... Cheers Tim "Ron Rosenfeld" wrote in message ... On Tue, 28 Mar 2006 20:33:32 -0800, "Tim Williams" <timjwilliams at gmail dot com wrote: What if a cell has 1 D word? Tim No problem. That's the reason for the REGEX.COUNT function. Note that the 'i' argument in the REGEX.MID function is for the instance of the occurrence. Note also that the If...Then in the middle is superfluous. It was present in an earlier version, but checking the number of words with the COUNT function eliminated the requirement to test the output; as the entire cell will be skipped if there is no D word. --ron |
Extracting word from phrase within column
KH_GS,
Private Sub CommandButton1_Click() Dim Cell As Range Dim Words As Variant Dim i As Long For Each Cell In Range(Range("A1"), Range("A1").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) If Right(Words(i), 1) = "d" Then Debug.Print Words(i) 'Or do what you want with the word End If Next Next End Sub NickHK "KH_GS" wrote in message ... I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases. Example: Column A red apple green apple green apple with seed orange pear Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be: red seed More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words. Ron Rosenfeld Wrote: On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS wrote: Hi All, I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column. Do I have to delimit the words by spaces first so I have only 1 word in each cell? How can I do it? Give some examples of cell contents and expected output. Multiple words in each source cell or a single word per cell? Also what kind of data size do you have (how many characters per cell; how many cells, on average)? --ron -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
Hi NickHK Thanks for the input! That about catches the whole thing! ;) I just added an input value line to list out the words: ActiveSheet.Cells(x, y).Value = Words(i) x = x + 1 Why do u make it private though? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
Private: The stub that Excel generates for a command button. But probably better to make it a public function, passing in the range to search, letter to find and an array to fill with matching words, returning the number of words found. Then dump the array to the desired location. NickHK "KH_GS" wrote in message ... Hi NickHK Thanks for the input! That about catches the whole thing! ;) I just added an input value line to list out the words: ActiveSheet.Cells(x, y).Value = Words(i) x = x + 1 Why do u make it private though? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
hi NickHK I tried changing the if statement to: If Left(Words(i), 3) = Left(Words(i + 1), 3) Then Theres a runtime error 9, out of range. I want to try comparing cell content instead of matching with my specified word. Also, how do I change this line For Each Cell In Range(Range("A2"), Range("A2").End(xlDown)) to something more dynamic, where the range begins at the cell i click on before running the macro. Code: -------------------- Private Sub CommandButton1_Click() Dim Cell As Range Dim Words As Variant Dim i As Long For Each Cell In Range(Range("A1"), Range("A1").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) If Right(Words(i), 1) = "d" Then Debug.Print Words(i) 'Or do what you want with the word End If Next Next End Sub -------------------- -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
That's because you are trying to access an element of the array beyond its UBound, which obviously does not exist. i.e. If i refers to the last element in the array, then i+1 will beyond the array's bound. So you would need some check to make sure i<UBound(Words()), then i+1 cannot be UBound(Words()). Assuming you want it from the selected cell to the end of the list: For Each Cell In Range(Selection, Selection.End(xlDown)) NickHK "KH_GS" wrote in message ... hi NickHK I tried changing the if statement to: If Left(Words(i), 3) = Left(Words(i + 1), 3) Then Theres a runtime error 9, out of range. I want to try comparing cell content instead of matching with my specified word. Also, how do I change this line For Each Cell In Range(Range("A2"), Range("A2").End(xlDown)) to something more dynamic, where the range begins at the cell i click on before running the macro. Code: -------------------- Private Sub CommandButton1_Click() Dim Cell As Range Dim Words As Variant Dim i As Long For Each Cell In Range(Range("A1"), Range("A1").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) If Right(Words(i), 1) = "d" Then Debug.Print Words(i) 'Or do what you want with the word End If Next Next End Sub -------------------- -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS
To satisfy our (my) curiosity, can you let us know why you're doing this? Seems like a v. large amount of data you are analyzing... Thanks Tim |
Extracting word from phrase within column
Part of a project, this stage is something about catching word i different tenses. Any idea to go about it? Tim Williams Wrote: KH_GS To satisfy our (my) curiosity, can you let us know why you're doing this? Seems like a v. large amount of data you are analyzing... Thanks Ti -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 |
Extracting word from phrase within column
Not my field at all, but wouldn't you be looking for words ending in "ed"
rather than just "d" ? Still, neither would distinguish "go/went", "come/came" and so on. You might want to implement some kind of lookup for those kinds of cases. Also, if you're going to be processing that much data you're probably better off just reading directly from a text file rather than cramming it all into Excel. -- Tim Williams Palo Alto, CA "KH_GS" wrote in message ... Part of a project, this stage is something about catching word in different tenses. Any idea to go about it? Tim Williams Wrote: KH_GS To satisfy our (my) curiosity, can you let us know why you're doing this? Seems like a v. large amount of data you are analyzing... Thanks Tim -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
If this is more to do with grammar than specific words/letters, I suspect there are better ways. An easy way would automate Word's spelling/grammar checker. Or there are a lot of grammar components out there. NickHK "KH_GS" wrote in message ... Part of a project, this stage is something about catching word in different tenses. Any idea to go about it? Tim Williams Wrote: KH_GS To satisfy our (my) curiosity, can you let us know why you're doing this? Seems like a v. large amount of data you are analyzing... Thanks Tim -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
And as Tim says, Excel may not be the best method. Databases (e.g. the free MySQL) that support full text search on BLOBs would probably prove more efficient on large amounts of text. I think you approach will need to more complex to retrieve meaningful results. For example, how you classify "closed" in "..a closed window.." ? or "..will be closed.." i.e. future not past. NickHK "NickHK" wrote in message ... KH_GS, If this is more to do with grammar than specific words/letters, I suspect there are better ways. An easy way would automate Word's spelling/grammar checker. Or there are a lot of grammar components out there. NickHK "KH_GS" wrote in message ... Part of a project, this stage is something about catching word in different tenses. Any idea to go about it? Tim Williams Wrote: KH_GS To satisfy our (my) curiosity, can you let us know why you're doing this? Seems like a v. large amount of data you are analyzing... Thanks Tim -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
Thanks for the suggestion. The data on hand is presented to me in Excel. Furthermore, there are other data tag to each line of word/phrase. This portion is only preliminary, with "d" or "ed" not that crucial at this moment as this require a minor alteration of the code when necessary. It is not necessary to analyze it as a phrase, just individual words. This might be further developed. Current objective is to generate list of words with similar spelling, perhaps by first 3 letters or last 3 etc, and to be fine tuned, with inputs of your suggestions. :) By the way should I macro a filter for removing cells that contain numbers only or just a manual action should do it? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS
"Current objective" always expand. I feel you would make you life more easy for the future, by starting on Regular Expressions, as your criteria and/or requirements become more complex. If you use a database, which is optomised for these processes, it would be better, but Excel can handle this. You could add a check: For Each Cell In Range(Range("A1"), Range("A1").End(xlDown)) If IsNumeric(Cell.Value) Then 'Do something here Else 'Continue as before Words = Split(Cell.Value, " ")..... If you are going to delete the rows that contain numeric values, then you should work from bottom to top. If you just ignore them, then it does not matter. NickHK "KH_GS" wrote in message ... Thanks for the suggestion. The data on hand is presented to me in Excel. Furthermore, there are other data tag to each line of word/phrase. This portion is only preliminary, with "d" or "ed" not that crucial at this moment as this require a minor alteration of the code when necessary. It is not necessary to analyze it as a phrase, just individual words. This might be further developed. Current objective is to generate list of words with similar spelling, perhaps by first 3 letters or last 3 etc, and to be fine tuned, with inputs of your suggestions. :) By the way should I macro a filter for removing cells that contain numbers only or just a manual action should do it? -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
Hi NickHK You r right on that, there is indeed further plans of expansion :cool Regarding the code that you input earlier, I need some help. Column A Column B apple green and red 1 apple blue and green 2 red green blue 3 Output: apple 1 green 1 and 1 red 1 apple 2 blue 2 and 2 green 2 red 3 green 3 blue 3 How can I input value of column B to a column beside the row of word that will be generated by the code below? Code ------------------- Sub PrintWords() Dim Cell As Range Dim Words As Variant Dim i As Long x = ActiveCell.Row y = ActiveCell.Column 'For Each Cell In Range(Selection, Selection.End(xlDown)) For Each Cell In Range(Range("A2"), Range("A2").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) Debug.Print Words(i) 'Or do what you want with the word ActiveSheet.Cells(x, y).Value = Words(i) x = x + 1 Next Next End Su ------------------- -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 |
Extracting word from phrase within column
Hi NickHK You r right on that, there is indeed further plans of expansion :cool Regarding the code that you input earlier, I need some help. Column A Column B apple green and red 1 apple blue and green 2 red green blue 3 Output: apple 1 green 1 and 1 red 1 apple 2 blue 2 and 2 green 2 red 3 green 3 blue 3 How can I input value of column B to a column beside the row of word that will be generated by the code below? Code ------------------- Sub PrintWords() Dim Cell As Range Dim Words As Variant Dim i As Long x = ActiveCell.Row y = ActiveCell.Column 'For Each Cell In Range(Selection, Selection.End(xlDown)) For Each Cell In Range(Range("A2"), Range("A2").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) Debug.Print Words(i) 'Or do what you want with the word ActiveSheet.Cells(x, y).Value = Words(i) x = x + 1 Next Next End Su ------------------- -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 |
Extracting word from phrase within column
KH_GS,
Add a line under: ActiveSheet.Cells(x, y).Value = Words(i) ActiveSheet.Cells(x, y+1).Value=Cell.Offset(0,1).Value or if you want the absolute row number ActiveSheet.Cells(x, y+1).Value=Cell.Row NickHK "KH_GS" wrote in message ... Hi NickHK You r right on that, there is indeed further plans of expansion :cool: Regarding the code that you input earlier, I need some help. Column A Column B apple green and red 1 apple blue and green 2 red green blue 3 Output: apple 1 green 1 and 1 red 1 apple 2 blue 2 and 2 green 2 red 3 green 3 blue 3 How can I input value of column B to a column beside the row of words that will be generated by the code below? Code: -------------------- Sub PrintWords() Dim Cell As Range Dim Words As Variant Dim i As Long x = ActiveCell.Row y = ActiveCell.Column 'For Each Cell In Range(Selection, Selection.End(xlDown)) For Each Cell In Range(Range("A2"), Range("A2").End(xlDown)) Words = Split(Cell.Value, " ") For i = 0 To UBound(Words) Debug.Print Words(i) 'Or do what you want with the word ActiveSheet.Cells(x, y).Value = Words(i) x = x + 1 Next Next End Sub -------------------- -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
Hi NickHK Thanks for the help once again. ;) I suppose there is something wrong with this line of code as it didn' seem to work: If Right(Words(i), 2) = "d?" does the ? mark make it wildcard or exact match? I had tried "d'" d with single quotation mark, it didn't catch too -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 |
Extracting word from phrase within column
KH_GS,
You want to use "LIKE" not "=". Also, your pattern is the wrong way around "?d". If this is the direction of your project, it really would be a good idea to read up on Regular Expressions. NickHK "KH_GS" wrote in message ... Hi NickHK Thanks for the help once again. ;) I suppose there is something wrong with this line of code as it didn't seem to work: If Right(Words(i), 2) = "d?" does the ? mark make it wildcard or exact match? I had tried "d'" d with single quotation mark, it didn't catch too. -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
No I wanted to get the word that ends with d as the second last letter. As for this code below, I get the error "next without for". Totall stumped. :confused: Code ------------------- Sub Match3Letters() Application.DisplayAlerts = False Application.ScreenUpdating = False x = ActiveCell.Row y = ActiveCell.Column For Each Cell In Range(Selection, Selection.End(xlDown)) If Left(Cells(x, y), 3) = Left(Cells(x + 1, y), 3) Then ActiveSheet.Cells(x, y + 3).Value = Cells(x, y) ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value ActiveSheet.Cells(x + 1, y + 3).Value = Cells(x + 1, y) ActiveSheet.Cells(x + 1, y + 4).Value = Cell.Offset(1, 1).Value x = x + 1 Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ------------------- NickHK Wrote: KH_GS, You want to use "LIKE" not "=". Also, your pattern is the wrong way around "?d". If this is the direction of your project, it really would be a goo idea to read up on Regular Expressions. NickHK "KH_GS" wrote in message ... Hi NickHK Thanks for the help once again. ;) I suppose there is something wrong with this line of code as i didn't seem to work: If Right(Words(i), 2) = "d?" does the ? mark make it wildcard or exact match? I had tried "d'" d with single quotation mark, it didn't catch too. -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread http://www.excelforum.com/showthread...hreadid=527411 -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php...fo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 |
Extracting word from phrase within column
KH_GS,
OK. Hint: You get that error when the block separators (or whatever they are collectively called) do not match. These a For Each...Next Do...While/Loop If.. Then..End If NickHK "KH_GS" wrote in message ... No I wanted to get the word that ends with d as the second last letter. As for this code below, I get the error "next without for". Totally stumped. :confused: Code: -------------------- Sub Match3Letters() Application.DisplayAlerts = False Application.ScreenUpdating = False x = ActiveCell.Row y = ActiveCell.Column For Each Cell In Range(Selection, Selection.End(xlDown)) If Left(Cells(x, y), 3) = Left(Cells(x + 1, y), 3) Then ActiveSheet.Cells(x, y + 3).Value = Cells(x, y) ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value ActiveSheet.Cells(x + 1, y + 3).Value = Cells(x + 1, y) ActiveSheet.Cells(x + 1, y + 4).Value = Cell.Offset(1, 1).Value x = x + 1 Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -------------------- NickHK Wrote: KH_GS, You want to use "LIKE" not "=". Also, your pattern is the wrong way around "?d". If this is the direction of your project, it really would be a good idea to read up on Regular Expressions. NickHK "KH_GS" wrote in message ... Hi NickHK Thanks for the help once again. ;) I suppose there is something wrong with this line of code as it didn't seem to work: If Right(Words(i), 2) = "d?" does the ? mark make it wildcard or exact match? I had tried "d'" d with single quotation mark, it didn't catch too. -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
yup i realised shortly after i posted that :eek: -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
This does not work. I made a mistake again. Will you please correct me? Range(Selection, Selection.End(xlLastCell)) I want the selection to be equivalent to ctrl + shift + end from cell A2. and then run the code for each cell like this. For Each Cell In Range(Selection, Selection.End(xlDown)) -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
Same but change Selection to Range("A2"). Voila. NickHK "KH_GS" wrote in message ... This does not work. I made a mistake again. Will you please correct me? Range(Selection, Selection.End(xlLastCell)) I want the selection to be equivalent to ctrl + shift + end from cell A2. and then run the code for each cell like this. For Each Cell In Range(Selection, Selection.End(xlDown)) -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
A little divert from the previous, checking the whole phrase in a cell for a match. I want to match words ending with "ing" and print the value of the whole cell containing such match in a new column. Data: apple running man red apple burning fire output: apple running man burning fire This code is not working :confused: Code: -------------------- Sub PrintEnd_ING() Dim Cell As Range Dim myString As String Application.DisplayAlerts = False Application.ScreenUpdating = False x = ActiveCell.Row y = ActiveCell.Column For Each Cell In Range(Selection, Selection.End(xlDown)) myString = Cells(x, y).Value If myString Like "*ing " Or myString = "*ing? " Then ActiveSheet.Cells(x, y + 3).Value = myString ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value x = x + 1 End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -------------------- -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
Extracting word from phrase within column
KH_GS,
What about: If InStr(myString,"ing ")) Then NickHK "KH_GS" wrote in message ... A little divert from the previous, checking the whole phrase in a cell for a match. I want to match words ending with "ing" and print the value of the whole cell containing such match in a new column. Data: apple running man red apple burning fire output: apple running man burning fire This code is not working :confused: Code: -------------------- Sub PrintEnd_ING() Dim Cell As Range Dim myString As String Application.DisplayAlerts = False Application.ScreenUpdating = False x = ActiveCell.Row y = ActiveCell.Column For Each Cell In Range(Selection, Selection.End(xlDown)) myString = Cells(x, y).Value If myString Like "*ing " Or myString = "*ing? " Then ActiveSheet.Cells(x, y + 3).Value = myString ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value x = x + 1 End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -------------------- -- KH_GS ------------------------------------------------------------------------ KH_GS's Profile: http://www.excelforum.com/member.php...o&userid=32920 View this thread: http://www.excelforum.com/showthread...hreadid=527411 |
| All times are GMT +1. The time now is 01:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com