Formula for extracting multiple occurrences of text substring in a cell

[EmLearnsMicrosoft]

Hi! I am trying to write a formula that will allow me to extract multiple strings of numbers from one cell to another, while filtering out all other irrelevant text from the source cell. For example (see attached photo), I want to be able to extract multiple 8-digit numbers, separated into the output column by "; ", that each occur after the text "ABCD: " within a cell. I have tried multiple complex nesting functions, based off of suggestions on other similar issues, but cannot seem to tailor the functions to fit my exact needs. Any help would be appreciated!!! (I use Excel for Mac, Version 16.84 (24041420))

Book2.xlsx
	A	B
1	Data Column 1	ABCD values extracted
2	Apple 2021 (ABCD: 12345678)	12345678
3	Boater 2021 (ABCD: 23456789), Crayon 2017 (ABCD: 34567890), Dorito 2010 (ABCD: 45678901), Developer Website: Grannies Cookies	23456789; 34567890; 45678901
4	Moose: Study immunity and effectiveness of Sample A (Apple 2021)
5	Moose: evaluate immunogenicity (Elbows 2021 (ABCD: 56789012)) Moose: evaluate infectivity, safety, immunogenicity and protective efficacy (Freefall 2021 (PMID: 67890123))	56789012; 67890123
Sheet1

 

[EmLearnsMicrosoft]

There must been some similar instances, but this will check if the numeric string is 8 characters long.

=UNIQUE(
   TEXTSPLIT(TEXTJOIN(";",,MAP(A2:B3,LAMBDA(m,
      LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),TEXTJOIN(";",,IF(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d,"")))))),,";"))

Data range.

Thank you so much for your ongoing help. This formula populated a "#VALUE" error and says "Error in Value". When I "Trace Error", it takes me to the very first cell in the array. The contents of that cell are as follows: "Tim 2021 (ABCD: 34925355)"

 

[EmLearnsMicrosoft]

Try this option as well. This doesn't rely on TEXTJOIN so it won't have a limit.

=UNIQUE(TOCOL(
      DROP(
           REDUCE("",TOCOL(A2:B3,1),LAMBDA(a,m,VSTACK(a,LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),IFS(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d))))),
      1),
2))

This formula populates a "#CALC" error and further says "Empty Array"

 

[Cubist]

Unfortunately it seems as though I do not yet have access to these functions (I updated the software today and tried).

It's only available on the Beta server right now.

This formula populates a "#CALC" error and further says "Empty Array"

Are you using Excel or Google Sheets? I've never seen that in Excel.

Book2
	A	B	C	D
1	Data Column 1
2	Tim 2021 (ABCD: 34925355)	Moose: Study immunity and effectiveness of Sample A (Apple 2021)		34925355
3	Boater 2021 (ABCD: 23456789), Crayon 2017 (ABCD: 34567890), Dorito 2010 (ABCD: 45678901), Developer Website: Grannies Cookies	Moose: evaluate immunogenicity (Elbows 2021 (ABCD: 56789012)) Moose: evaluate infectivity, safety, immunogenicity and protective efficacy (Freefall 2021 (PMID: 67890123))		23456789
4				34567890
5				45678901
6				56789012
7				67890123
Sheet3

Cell Formulas
Range	Formula
D2:D7	D2	=UNIQUE(TOCOL( DROP( REDUCE("",TOCOL(A2:B3,1),LAMBDA(a,m,VSTACK(a,LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),IFS(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d))))), 1), 2))
Dynamic array formulas.

 

[EmLearnsMicrosoft]

It's only available on the Beta server right now.


Are you using Excel or Google Sheets? I've never seen that in Excel.

Book2
	A	B	C	D
1	Data Column 1
2	Tim 2021 (ABCD: 34925355)	Moose: Study immunity and effectiveness of Sample A (Apple 2021)		34925355
3	Boater 2021 (ABCD: 23456789), Crayon 2017 (ABCD: 34567890), Dorito 2010 (ABCD: 45678901), Developer Website: Grannies Cookies	Moose: evaluate immunogenicity (Elbows 2021 (ABCD: 56789012)) Moose: evaluate infectivity, safety, immunogenicity and protective efficacy (Freefall 2021 (PMID: 67890123))		23456789
4				34567890
5				45678901
6				56789012
7				67890123
Sheet3

Cell Formulas
Range	Formula
D2:D7	D2	=UNIQUE(TOCOL( DROP( REDUCE("",TOCOL(A2:B3,1),LAMBDA(a,m,VSTACK(a,LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),IFS(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d))))), 1), 2))
Dynamic array formulas.

I'm using Excel. When I select "Help on this error" it says "#CALC! errors occur when Excel's calculation engine encounters a scenario it does not currently support."

 

[Cubist]

I'm using Excel. When I select "Help on this error" it says "#CALC! errors occur when Excel's calculation engine encounters a scenario it does not currently support."
View attachment 113998

Can you show exactly what you entered and your sheet?

 

[EmLearnsMicrosoft]

Can you show exactly what you entered and your sheet?

Sure; what all on the sheet do you need to see? The array of data is large (B2:DT211) so won't be able to share everything

Here's are the formula's though (copy & pasted straight from excel):

1. The one that resulted in a #VALUE error:
=UNIQUE(TEXTSPLIT(TEXTJOIN(";",,MAP(B2:DT211,LAMBDA(m,LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),TEXTJOIN(";",,IF(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d,"")))))),,";"))

2. The one that resulted in a #CALC! error:
=UNIQUE(TOCOL(DROP(REDUCE("",TOCOL(B2:DT211,1),LAMBDA(a,m,VSTACK(a,LET(t,TEXTSPLIT(m,,{" ",")"},1),d,DROP(t,1),IFS(ISNUMBER(FIND(":",DROP(t,-1)))*ISNUMBER(--d)*(LEN(d)=8),d))))),1),2))

 

[Cubist]

Your list is probably too long and exceeds the row limit in Excel. Try it with a smaller range and see if that's the issue.

 

[EmLearnsMicrosoft]

Your list is probably too long and exceeds the row limit in Excel. Try it with a smaller range and see if that's the issue.

You're correct. List was too long. Thank you!

 

[Cubist]

You're welcome. I'd go with the second formula as it's less restrictive.

 

[Sektor]

For now regular expression functions are available for insider users (Beta Channel). Here's the news about them.