1
votes

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.

2
Add at least one example of the complete values that appear in the spreadsheet cells or a print with example values, making it easier to see.Brondby IF

2 Answers

1
votes

try:

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

enter image description here

1
votes

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:

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:

output