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

Question

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}"))

Try `(?:\+|00)?(?:30)?\d{10}`, or `(?:\+|00)?(?:30)?69\d{8}` — Wiktor Stribiżew, Oct 29 '19 at 09:08
both of them work, thanks! only issue: it also extracts the country code (+30, 30, 0030), but this is not a problem for me. — thanasix, Oct 29 '19 at 09:49
See [my answer below](https://stackoverflow.com/a/58604849/3832970). You need to wrap the part you need to extract with a pair of parentheses. — Wiktor Stribiżew, Oct 29 '19 at 09:51
@WiktorStribiżew, how can i verify your solution if it is in a comment? — thanasix, Oct 29 '19 at 09:53

Wiktor Stribiżew · Accepted Answer · 2019-10-29T09:50:43.213

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.

Michael Oosthuizen · Answer 2 · 2019-10-29T09:17:16.343

1

The solution to your problem is to make use of a regex lookbehind (although I do not know if Google Sheets supports this).

A regex lookbehind matches a pattern, but without including in the result. The syntax for this, with your example, is:

(?<=69)\d{10}

The picture below is taken from https://regex101.com/ (which is a super-useful tool when working with regexps).

Regex lookahead, lookbehind and atomic groups has some more examples of how lookaheads and lookbehinds work.

edited Oct 29 '19 at 09:17

answered Oct 29 '19 at 09:11

Google sheets regex functions use RE2 regex library that does not support lookarounds. At regex101.com, the relevant option is Go (it also uses RE2). – Wiktor Stribiżew Oct 29 '19 at 09:13
1

Ah, in that case my solution is quite useless :| – Michael Oosthuizen Oct 29 '19 at 09:18
As you can yourself see, [your regex throws an error](https://regex101.com/r/PSEeif/1) – Wiktor Stribiżew Oct 29 '19 at 09:19

player0 · Answer 3 · 2019-10-29T10:21:27.383

0

all you need is:

=ARRAYFORMULA(IFNA(REGEXREPLACE(REGEXEXTRACT(A1:A&""; "69.*"); "\s|/|\D+"; )))

or better:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(REGEXREPLACE(A1:A&""; "\D+"; ); "69.{8}")))

edited Oct 29 '19 at 10:21

answered Oct 29 '19 at 09:55

player0

it works but it also extracts everything after the mobile number – thanasix Oct 29 '19 at 10:07
very nice! the only problem i found in CVs that have an irrelevant 69 before the mobile: eg. Name:XXX, Surname:YYY Address: Street name 69, Mobile: 6944xxxxxx. The output of the Regextract is: 696944xxxx – thanasix Oct 29 '19 at 16:16

3 Answers3