Home 
Search 
Today's Posts 
#1




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




Excel formula needed... regular expression?
a) Place the text strings in column A, starting at row 2
b) Enter number 97122 in columns BAA 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 B2AB2 e) copy down all columns f) Copy AB:AB, Paste Paste special Values g) Delete columns BAA "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




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 97122 in columns BAA 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 B2AB2 e) copy down all columns f) Copy AB:AB, Paste Paste special Values g) Delete columns BAA "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




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: <altF11 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) 