Feb 24 2022 07:42 AM
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!
Feb 24 2022 08:24 AM
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")
Feb 25 2022 08:37 AM
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
Feb 25 2022 09:01 AM
@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'.
Feb 25 2022 09:04 AM - edited Feb 25 2022 09:13 AM
@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.
Feb 25 2022 09:31 AM
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.
Feb 25 2022 11:26 AM
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
Mar 02 2022 06:35 AM