Excel - Excel formula needed... regular expression?

Asked By danes on 09-Aug-07 11:06 PM
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"

Tevun replied on 09-Aug-07 11:48 PM
a) Place the text strings in column A, starting at row 2
b) Enter number 97-122 in columns B-AA in row 1. In cell AB1, enter 32
c) Paste this formula in cell B2:
d) copy across row 2, from B2-AB2
e) copy down all columns
f) Copy AB:AB, Paste > Paste special > Values
g) Delete columns B-AA
Tevun replied on 09-Aug-07 11:54 PM
Finally subtitute all "T" with ", T"
=SUBSTITUE(B2,"T"," ,T")
You could also do it in column AC, together with the rest, it is just that I
Ron Rosenfeld replied on 10-Aug-07 07:31 AM
On Thu, 9 Aug 2007 20:06:03 -0700, danesh <danesh@>

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:

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.


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