How to extract a word from phrase from Excel columns

[Kumar esan]

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.

[BillieJ]

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

[JustineBB]

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

[-Deven-]

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.

[Go!pee]

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

[Panop]

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.