Remember Me?

#1
August 10th 07, 04:06 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2007 Posts: 2
Excel formula needed... regular expression?

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"

#2
August 10th 07, 04:48 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 136
Excel formula needed... regular expression?

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:
=SUBSTITUTE(A2,CHAR(B\$1),"")
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

"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"

#3
August 10th 07, 04:54 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 136
Excel formula needed... regular expression?

Finally subtitute all "T" with ", T"
=SUBSTITUE(B2,"T"," ,T")
You could also do it in column AC, together with the rest, it's just that I
forgot.

"Tevuna" wrote:

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:
=SUBSTITUTE(A2,CHAR(B\$1),"")
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

"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"

#4
August 10th 07, 12:31 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,651
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post sl Excel Discussion (Misc queries) 2 January 24th 07 12:57 AM GarrettD78 Excel Worksheet Functions 3 November 2nd 05 12:21 AM Mike Excel Worksheet Functions 2 October 11th 05 07:12 PM ExcelLEarner New Users to Excel 1 December 5th 04 05:54 AM Yubasus Excel Discussion (Misc queries) 2 November 26th 04 01:41 AM

All times are GMT +1. The time now is 09:47 PM.