New Contributor

# Excel Formula

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

# Re: Excel Formula

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

# Re: Excel Formula

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

# Re: Excel Formula

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

# Re: Excel Formula

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

# Re: Excel Formula

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.

# Re: Excel Formula

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

# Re: Excel Formula

Hi Sergei,

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