extracting 6 digit number from text field

[Tipsey]

I have a list of employee names followed by their employee number, followed by their starting shift times.
eg.

Bob Ross 123456 530 530 730 730 530
Bruce (batman) Wayne 223344 1130 1130 1130 1130 1200

etc.

I would like to extract only their employee number.

Thanks in advance.

 

[Jeffrey Mahoney]

Please try this:
=FILTER(TEXTSPLIT(L4," ",,TRUE),LEN(TEXTSPLIT(L4," ",,TRUE))=6,"")
Where L4 is the cell to evaluate

 

[Jeffrey Mahoney]

Or even a shorter version:
=LET(TS,TEXTSPLIT(L4," ",,TRUE),FILTER(TS,LEN(TS)=6,""))

 

[Fluff]

Another option

Fluff.xlsm
	A	B
1
2	Bob Ross 123456 530 530 730 730 530	123456
3	Bruce (batman) Wayne 223344 1130 1130 1130 1130 1200	223344
Main

Cell Formulas
Range	Formula
B2:B3	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number() and string-length()=6]")