Excel Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3210102%22%20slang%3D%22en-US%22%3EExcel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3210102%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTRIM(LEFT(SUBSTITUTE(MID(B3%2CFIND(%22Appx%22%2CB3)%2CLEN(B3))%2C%22%20%22%2CREPT(%22%20%22%2C100))%2C100))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20extracts%20all%20alph-numeric%20hits%20containing%20Appx123%2C%20Appx123-456%20etc.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20only%20extacts%20the%20first%20hit%20and%20no%20others%20in%20the%20search%20cell%20(the%20cell%20sometimes%20contains%20multiple%20Appx%20nos).%26nbsp%3B%20I%20want%20to%20have%20the%20formula%20extract%20all%20hits%20from%20the%20cell%20and%20put%20them%20on%20seperate%20rows%20in%20my%20excel%20sorksheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3210102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3210377%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3210377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316900%22%20target%3D%22_blank%22%3E%40Dannyjbrown_56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20ExtractAll(Text%20As%20String%2C%20Phrase%20As%20String)%0A%20%20%20%20Dim%20re%20As%20Object%0A%20%20%20%20Dim%20m%0A%20%20%20%20Dim%20v%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20arr()%20As%20String%0A%20%20%20%20Set%20re%20%3D%20CreateObject(Class%3A%3D%22VBScript.RegExp%22)%0A%20%20%20%20re.Pattern%20%3D%20%22Appx%5B0-9%5C-%5D%7B1%2C%7D%20%22%0A%20%20%20%20re.Global%20%3D%20True%0A%20%20%20%20Set%20m%20%3D%20re.Execute(Text)%0A%20%20%20%20ReDim%20arr(1%20To%20m.Count%2C%201%20To%201)%0A%20%20%20%20For%20Each%20v%20In%20m%0A%20%20%20%20%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20%20%20%20%20arr(i%2C%201)%20%3D%20v%0A%20%20%20%20Next%20v%0A%20%20%20%20ExtractAll%20%3D%20arr%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3DExtractAll(B3%2C%22Appx%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3214226%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316900%22%20target%3D%22_blank%22%3E%40Dannyjbrown_56%3C%2FA%3E%20After%20copying%20the%20code%20into%20a%20module%2C%20switch%20back%20to%20Excel%2C%20and%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DExtractAll(B3%2C%22Appx%22)%3C%2FP%3E%0A%3CP%3Einto%20the%20first%20cell%20where%20you%20want%20the%20result%2C%20for%20example%20in%20C3.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20save%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(*.xlsm).%3C%2FP%3E%0A%3CP%3EYou%20can%20set%20macro%20options%20in%20File%20%26gt%3B%20Options%20%26gt%3B%20Trust%20Center%20%26gt%3B%20Trust%20Center%20Settings...%20%26gt%3B%20Macro%20Settings.%3C%2FP%3E%0A%3CP%3EThe%20setting%20I%20recommend%20is%20'Disable%20VBA%20macros%20with%20notification'.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3214327%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316900%22%20target%3D%22_blank%22%3E%40Dannyjbrown_56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECharles%20Williams%20offers%20a%20very%20effective%20paid%20add-in%20FastExcel%20which%20extends%20Excel's%20built-in%20function%20library%20with%20very%20effective%20and%20highly%20performant%20extensions%20such%20as%20Regular%20Expressions.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20Rgx.MID(%40TextArray%2C%20%22Appx%5B0-9%5C-%5D%2B%22%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eworks%20with%20a%20single%20term%20and%20with%20modern%20Excel%20this%20may%20be%20extended%20as%20an%20array%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAKEARRAY(3%2C2%2C%0A%20%20%20%20%20%20LAMBDA(r%2Cc%2C%0A%20%20%20%20%20%20%20%20%20Rgx.MID(INDEX(TextArray%2Cr)%2C%20%22Appx%5B0-9%5C-%5D%2B%22%2Cc)%0A%20%20%20%20%20%20)%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ehere%20used%20to%20address%202%20elements%20from%20an%20array%20of%203%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3214698%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1316900%22%20target%3D%22_blank%22%3E%40Dannyjbrown_56%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20VBA%20version%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.linkedin.com%2Fpulse%2Ffind-text-matching-specified-pattern-rick-rothstein-1f%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EFind%20Text%20Matching%20A%20Specified%20Pattern%3C%2FA%3E%26nbsp%3B%20by%20Rick%20Rothstein%26nbsp%3B%20of%20any%20pattern%20extraction%20and%20its%20lambda%20clone%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.linkedin.com%2Fpulse%2Ffollow-rick-rothstein-lambda-attempt-sergei-baklan%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EFollow%20Rick%20Rothstein%20-%20lambda%20attempt%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am using the formula:

 

=TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("Appx",B3),LEN(B3))," ",REPT(" ",100)),100))

 

The formula extracts all alph-numeric hits containing Appx123, Appx123-456 etc.  

 

The formula only extacts the first hit and no others in the search cell (the cell sometimes contains multiple Appx nos).  I want to have the formula extract all hits from the cell and put them on seperate rows in my excel sorksheet. 

 

Thanks much!

7 Replies

@Dannyjbrown_56 

Here is a custom VBA function:

Function ExtractAll(Text As String, Phrase As String)
    Dim re As Object
    Dim m
    Dim v
    Dim i As Long
    Dim arr() As String
    Set re = CreateObject(Class:="VBScript.RegExp")
    re.Pattern = "Appx[0-9\-]{1,} "
    re.Global = True
    Set m = re.Execute(Text)
    ReDim arr(1 To m.Count, 1 To 1)
    For Each v In m
        i = i + 1
        arr(i, 1) = v
    Next v
    ExtractAll = arr
End Function

Use like this:

=ExtractAll(B3,"Appx")

@Hans Vogelaar 

 

Thank you very much!  Although I may of bit off a little too much!   Can you lead me along to get into excel?

 

1st - I selected the cell I wanted to extract from, hit alt-F11, added the code to module, closed the module, and went back to spreadsheet.  What's next or did I already overlook something?  Another question, how do I configure Macro Security?

 

Thank for for all the help!

 

Dan

@Dannyjbrown_56 After copying the code into a module, switch back to Excel, and enter the formula

=ExtractAll(B3,"Appx")

into the first cell where you want the result, for example in C3.

 

Make sure that you save the workbook as a macro-enabled workbook (*.xlsm).

You can set macro options in File > Options > Trust Center > Trust Center Settings... > Macro Settings.

The setting I recommend is 'Disable VBA macros with notification'.

@Dannyjbrown_56 alternatively you could use this in-cell formula (or convert it to Lambda function)

 

=LET(in,H1,
         str,"Appx",
         inLen, LEN(in),
         sub,SUBSTITUTE(in,str,REPT(" ",inLen)&str),
         nFound,LEN(sub)/inLen-1,
         out,IF(nFound,TRIM(MID(sub,SEQUENCE(nFound,1,inLen,inLen),inLen)),""),
         LEFT(out,SEARCH(" ",out&" ")))

 

and if you don't have Excel 2021 or newer it can get converted to older functions.

 

@Dannyjbrown_56 

Charles Williams offers a very effective paid add-in FastExcel which extends Excel's built-in function library with very effective and highly performant extensions such as Regular Expressions.

= Rgx.MID(@TextArray, "Appx[0-9\-]+",0)

works with a single term and with modern Excel this may be extended as an array formula

= MAKEARRAY(3,2,
      LAMBDA(r,c,
         Rgx.MID(INDEX(TextArray,r), "Appx[0-9\-]+",c)
      )
   )

here used to address 2 elements from an array of 3 values.

 

@Dannyjbrown_56 

Here is VBA version  Find Text Matching A Specified Pattern  by Rick Rothstein  of any pattern extraction and its lambda clone Follow Rick Rothstein - lambda attempt    

@Sergei Baklan 

 

Hi Sergei,

 

Just curious if you saw my last reply, thanks much!!!