Extract multiple strings from the same cell using Formulas

0
votes

I have contents of one excel cell as: for example

Bananas_05/05/2019
Oranges_07/06/2019
Oranges_08/05/2019
Apples_10/12/2018
Oranges_08/03/2019

I want, using formulas only (no VBA) to extract all the dates next to "Oranges_" So I want the output to be:

07/06/2019
08/05/2019
08/03/2019

My current formula can extract only 1 of them, not all

I have no idea what I can try

This is what I have: DATEVALUE(MID(D20,FIND("Oranges_",D20)+LEN("Oranges_")+1,10))

1
excelformula

1 Answers

0
votes

This is only possible if one has TEXTJOIN() which was introduced with OFFICE 365 Excel.

If one has TEXTJOIN() then this array form will iterate each line and test then concatenate the dates.

=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH("Oranges_",TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",999)),ROW($XFD$1:INDEX($XFD:$XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))*999+1,999)))),SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",999)),ROW($XFD$1:INDEX($XFD:$XFD,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))*999+1,999)),"Oranges_",""),""))

Being an Array Formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here