View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Excel formula needed... regular expression?

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