Find numbers that are 9 or 10 characters long in mixed string

[Chrissy_M]

I have a mixed variety of cell contents, whereby multiple number references appear.
I am looking for a formula to extract any numbers within a cell that is 9 or 10 characters long.
If the number begins with a zero, I would like the zeros to be included in the results.

Example in lines 2, and 3 below show additional numbers (2 and 1, respectively), I do not want any number/s that are not 9 or 10 characters long returned in the results.

For example, line 3 results I am trying to get would be: 0000065234

Appreciate any help with this one - thank you

The cat went 0000052489 out to play

The black dog 1000035687 slept under window number 2

The bird with 1 eye was stuck in it's cage 0000065234

 

[Skyybot]

Where do you want the extracted data to go? Another Sheet? Another column on the same Sheet?

 

[hagia_sofia]

Hello, how about:

=MAP(A1:A3,LAMBDA(x,
LET(
a,TEXTSPLIT(x," "),
b,(LEN(a)>=9)*(LEN(a)<=10)*ISNUMBER(--a),
FILTER(a,b=1,""))))

 

[Chrissy_M]

Hi there, in cell alongside, say A2, with mixed character string in column B

Where do you want the extracted data to go? Another Sheet? Another column on the same Sheet?

 

[Chrissy_M]

To clarify, top row indicates columns, left column indicates rows.
I would like formula in column A to reference cells in column B, and return 9 or 10 character number strings only, ignoring other numerical references within the string.

Hope this helps clarify my original post - thanks

	A	B
1	0000052489	The cat went 0000052489 out to play
2	1000035687	The black dog 1000035687 slept under window number 2
3	0000065234	The bird with 1 eye was stuck in it's cage 0000065234

 

[Chrissy_M]

Hello, how about:

=MAP(A1:A3,LAMBDA(x,
LET(
a,TEXTSPLIT(x," "),
b,(LEN(a)>=9)*(LEN(a)<=10)*ISNUMBER(--a),
FILTER(a,b=1,""))))

Hi there, I think we nearly got it. Can we adjust it to only return 10 character numbers only. ie. if the numbers are followed by a full stop, I do not want the full stop added to the results. Some of the 9 digit numbers have returned full stops as well and I do not want this included

 

[hagia_sofia]

Hello, many thanks for the feedback. Indeed, I have not considered that option, the adjusted formula only extracts 10 digit numbers and should even work where the number is followed by either "." or ",":

=MAP(B1:B3,LAMBDA(x,
LET(
a,TEXTSPLIT(x,{" ",".",","}),
b,(LEN(a)=10)*(ISNUMBER(--a)),
FILTER(a,b=1,""))))

P. S. Is it possible that there are 2 or more 10-digit numbers in a single cell?