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

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

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

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