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
