Get formula that pulls a number from a sentence in a cell

[Jyggalag]

Hi all!

I have this setup (albeit much larger in reality):

Every statement has a sentence with "XYZ employees".

Is there a formula that can detect when a sentence says a number, followed by the string value "employees", and then return the value of the number? So that formula can fill out what is currently column C in the picture above.

Would be immensely helpful!

I hope that my request makes sense, otherwise please feel free to ask me questions and I will be happy to elaborate

Kind regards,
Jyggalag

 

[Fluff]

Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JamesCanale]

365 solution. There are problems - like the fourth one where the word employee comes up more than once. I left columns E, F, and G in there to see it a bit more clearly.

MrExcelPlayground8.xlsx
	B	C	D	E	F	G
2	The company noted that 3 employees left after december 2021	3		25	The company noted that 3	3
3	He wanted to fire all 5 employees left in his division, which had a total of 10 people in it.	5		24	He wanted to fire all 5	5
4	They decided to setup a team of 10 employees to work on the project.	10		35	They decided to setup a team of 10	10
5	The employees didn't like the other 10 employees.	0		4	The	0
6	I am very tall.	0		#VALUE!	#VALUE!	0
7	There is 1 employee in there.	1		11	There is 1	1
Sheet8

Cell Formulas
Range	Formula
E2:E7	E2	=SEARCH(" employee",B2)
F2:F7	F2	=LEFT(B2,E2-1)
G2:G7	G2	=MAX(IFERROR(VALUE(RIGHT(F2,SEQUENCE(LEN(F2)))),0))
C2:C7	C2	=MAX(IFERROR(VALUE(RIGHT(LEFT(B2,SEARCH(" employee",B2)-1),SEQUENCE(LEN(LEFT(B2,SEARCH(" employee",B2)-1))))),0))

 

[Fluff]

Another option using James's data

+Fluff 1.xlsm
	A	B
1
2	The company noted that 3 employees left after december 2021	3
3	He wanted to fire all 5 employees left in his division, which had a total of 10 people in it.	5
4	They decided to setup a team of 10 employees to work on the project.	10
5	The employees didn't like the other 10 employees.	10
6	I am very tall.
7	There is 1 employee in there.	1
Lists

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'employee')]/preceding::m[.=number()]"),"")

 

[Jyggalag]

Another option using James's data

+Fluff 1.xlsm
	A	B
1
2	The company noted that 3 employees left after december 2021	3
3	He wanted to fire all 5 employees left in his division, which had a total of 10 people in it.	5
4	They decided to setup a team of 10 employees to work on the project.	10
5	The employees didn't like the other 10 employees.	10
6	I am very tall.
7	There is 1 employee in there.	1
Lists

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'employee')]/preceding::m[.=number()]"),"")

Amazing!

Thank you so much Fluff, and James!

Much appreciated! And noted Fluff, will try to upload like James did going forward

 

[Fluff]

Glad we could help & thanks for the feedback.