I want to find a text portion match in a cell. If there is a match, I want
to extract the text that follows. For example,
If there is the following text in a cell:
abc def (ghi) jkl

I search for "def", then I want to extract the text "ghi" and copy this to
another cell.

look at the post titled "string matching" from 6-5 and see if any of the replies
may help

--


Gary


"pcd81" wrote in message
...
I want to find a text portion match in a cell. If there is a match, I want
to extract the text that follows. For example,
If there is the following text in a cell:
abc def (ghi) jkl

I search for "def", then I want to extract the text "ghi" and copy this to
another cell.

On Fri, 8 Jun 2007 12:09:00 -0700, pcd81
wrote:

I want to find a text portion match in a cell. If there is a match, I want
to extract the text that follows. For example,
If there is the following text in a cell:
abc def (ghi) jkl

I search for "def", then I want to extract the text "ghi" and copy this to
another cell.

When you write "extract the text that follows", what exactly do you mean?

In your example, you reference "ghi" but the characters that follow "def" are
(ghi) jkl

So you are not extracting the parentheses, nor the "jkl".

The following UDF will return "ghi" from your string "abc def (ghi) jkl"

You may need to be more explicit in your requirements, if this does not do what
you want. Basically, it returns the "word" that follows your string. A word
is defined as a series of characters in the class [A-Za-z0-9_]

So "ghi" would also be extracted from the string: "abc defghi jkl"

================================================
Option Explicit

Function NextWord(str As String, TextToFind As String) As String
Dim oRegex As Object
Dim mcMatchCollection As Object
Dim sPattern As String

sPattern = TextToFind & ".*?(\w+)"

Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.IgnoreCase = True
.Pattern = sPattern
End With

If oRegex.Test(str) = True Then
Set mcMatchCollection = oRegex.Execute(str)
NextWord = mcMatchCollection(0).SubMatches(0)
End If

End Function
=================================================

--ron