Q:

Google sheets formula to extract 10-digit mobile number starting from 69

I want to extract the mobile phones from candidates' CVs.
The mobile phone format I want to extract is 69xxxxxxxx.
The mobile phone formats i come across in the CVs are:
69 xxx xxxxx
0030 69xxxxxxxx
+3069xxxxxxxx
69/xxxx/xxxx

The following formula works great but it extracts the first 10 digits detected and not the one that starts with 69.
=IFERROR(REGEXEXTRACT(TO_TEXT(SPLIT(REGEXREPLACE(I252;"\(|\)|\-| "; ""); CHAR(10))); "\d{10}"))

A:

You may use
=IFERROR(REGEXEXTRACT(TO_TEXT(SPLIT(REGEXREPLACE(I252;"[-/() ]+"; ""); CHAR(10))); "(?:\+|00)?(?:30)?(69\d{8})"))

See the regex demo and the Google Sheets screenshot below:

The regex matches

(?:\+|00)? - an optional + or 00
(?:30)? - an optional 30
( - start of the capturing group (only this value will be returned):

69 -  69 value
\d{8} - eight digits

) - end of the group.

You might consider appending \b at the end of the regex to avoid matching the 8 digits in chunks of more than 8 digits.
Note that the separator cleaning regex is [-/() ]+ now, it matches 1 or more -, /, (, ) and spaces.

