On Thu, 9 Aug 2007 20:06:03 -0700, danesh
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