If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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" 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Regular Expression  sl  Excel Discussion (Misc queries)  2  January 23rd 07 11:57 PM 
Counting Expression in a Single formula  GarrettD78  Excel Worksheet Functions  3  November 1st 05 11:21 PM 
Excel expression {=SUm(if(istext(c8:k8),1,0))}  Mike  Excel Worksheet Functions  2  October 11th 05 07:12 PM 
> and < criteria in one single expression in excel  ExcelLEarner  New Users to Excel  1  December 5th 04 04:54 AM 
Excel Expression  Yubasus  Excel Discussion (Misc queries)  2  November 26th 04 12:41 AM 