Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regular Expression | Excel Discussion (Misc queries) | |||
Counting Expression in a Single formula | Excel Worksheet Functions | |||
Excel expression {=SUm(if(istext(c8:k8),1,0))} | Excel Worksheet Functions | |||
> and < criteria in one single expression in excel | New Users to Excel | |||
Excel Expression | Excel Discussion (Misc queries) |