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