player0's answer is already good. But if you only need to extract 10 digit numbers and not include other numbers in the cell (e.g. 123
), make sure to exclude the non-10 digit numbers.
I did modify the other answer to filter out those non-10 digit numbers using another regexreplace
before using split
.
Formula:
=split(regexreplace(regexreplace(A1,"\D+", " "),
"^\d{1,9}\s|\s\d{1,9}\s|\s\d{1,9}$|\d{11,}"
, " "), " ")
Patterns to exclude:
We need to exclude any numbers that aren't 10 succeeding digits. These are the following possible patterns.
^\d{1,9}\s
less than 10 numbers at the start
\s\d{1,9}\s
less than 10 number in between
\s\d{1,9}$
less than 10 numbers at the end
\d{11,}
more than 10 numbers
Appending them all using |
resulting into "^\d{1,9}\s|\s\d{1,9}\s|\s\d{1,9}$|\d{11,}"
Sample Cell Value:
123asd1234567890oia123joieqw9876543210asda123asd12345678910
Output:

EDIT:
It seems it is having an issue on multiple occurrences when the string has spaces in between. If script is an option, I do recommend this one below.
Code:
function get10DigitNums(string) {
var regex = /[^\w](\d{10})[^\w]|^(\d{10})[^w]|[^w](\d{10})$/g;
var result = [];
do {
m = regex.exec(string);
if (m) {
m.shift();
result.push(m);
}
} while (m);
return [result.flat().filter(Boolean)];
}
Output:
