how to extract 10 digit phone number from a cell in Google sheet

Question

I have multiple rows on Google sheet, each cell in the row contains emails messages which have loads of text and symbols etc and also phone numbers, I need to extract these phone numbers, the phone numbers are 10 digits no spaces.

I tried regexextract on google sheet but it gives me only the first number

=REGEXEXTRACT(E2,"\d+")

how do I extract multiple phone numbers data which are present in each cell on Google sheet.

Answer 1

try:

=IFERROR(SPLIT(REGEXREPLACE(A1:A, "\D+", " "), " "))

Answer 2

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.

1. ^\d{1,9}\s less than 10 numbers at the start
2. \s\d{1,9}\s less than 10 number in between
3. \s\d{1,9}$ less than 10 numbers at the end
4. \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: