Excel: Extract a sequence of n numbers from a string with a formula

Question

I have a cell in excel which holds a 6 digit number which I need to extract. That cell may contain stings or other numbers but fortunately no other number of 6 digits. (spacing, number of words, location number may vary) Is there a way to extract that 6 digit number only?

I cannot use VBA for this, if must be a formula

Cell A1	Cell B1
ab 12 abcd 1234 abcdef 123456 abcdefgh 12345678	123456

Answer 1

It's safer to test each individual character within the parsed substrings for numericalness. Otherwise, substrings such as 2-june or 1.2E04 can be returned undesirably.

=LET(
    ζ,TEXTSPLIT(A1," "),
    FILTER(ζ,(LEN(ζ)=6)*MMULT(SEQUENCE(,6,,0),1-ISERR(0+MID(ζ,SEQUENCE(6),1)))=6)
)

That said, the following should also be sufficiently rigorous:

=LET(ζ,TEXTSPLIT(A1," "),FILTER(ζ,(LEN(ζ)=6)*(1-ISERR(0+(ζ&"**0")))))

though the latter will return decimals such as 1.2345 whereas the former won't.