Excel Formula

[Dannyjbrown_56]

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!

[#2]

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

[#3]

@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

[#4]

@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'.

[#5]

@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.

[#6]

@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.

[#7]

@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    

[#8]

@Sergei Baklan 

Hi Sergei,

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