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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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.



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

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.



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

yup i realised shortly after i posted that


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

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

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

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

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

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

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