Re: Excel formula needed... regular expression?

On Thu, 9 Aug 2007 20:06:03 -0700, danesh <danesh@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

A1 contains "TC_1 and TC_2 are really cool TC_5 and TC_6, but TC_7 is better"

B1 should use a formula to generate "TC_1, TC_2, TC_5, TC_6, TC_7"

There are 550 rows, so the formula should be flexible enough to apply to "N"
number of matches, and insert the matches into column B corresponding to the
entry in column A of the same row.

A2 contains "TC_1 and TC_22 are really cool TC_5"
B2 should use a formula to generate "TC_1, TC_22, TC_5"

Here is a UDF to do extract the strings (defined by a regular expression) and
concatenate with a user determined separator.

B1: =ExtrConcat(A1,"TC_\d+",",")

To make this work, you must enter the code in a regular module:

<alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
From the top menu, select Tools/References and then check Microsoft VBSCript
Regular Expressions 5.5.

Enjoy


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

Function ExtrConcat(str As String, sPattern As String, _
Optional sSeparator As String = " ") As String
'Requires setting a Reference to Microsoft VBScript Regular Expressions 5.5
Dim re As RegExp
Dim mc As MatchCollection
Dim ma As Match

Set re = New RegExp
With re
.Global = True
.Pattern = sPattern
.IgnoreCase = False
.MultiLine = True
End With

If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each ma In mc
ExtrConcat = ExtrConcat & ma & sSeparator
Next ma
End If
ExtrConcat = Left(ExtrConcat, Len(ExtrConcat) - 1)
End Function
======================================


--ron
.

Loading