Search code examples
regexgoogle-sheetsgoogle-sheets-formulare2

Extract fixed length string between two numbers

I have this number: 003859389453604802410207622210986832370060. In this instance, I need to extract 07622210986832 which comes before 02 and ends with 37.

In the real world, 07622210986832 is always 14 digits, and will always start with 02 and end with 37 BUT it could appear at any point in a string that is of random length - all we know is that the number will be there somewhere.

I'm currently using the formula:

 =IF(LEN(IFERROR(REGEXEXTRACT(A1:A&"", "02(.*)37")))=14,

However, you will notice in the number sample there is another 02 - "024102". This is causing an issue.

What I really want to happen is:

  • Lookup 02
  • Find the string of 14 numbers and if number 15 is 3 and 16 is 7 (37), that is the number we need.
  • If you find another 02 number with a 14 digit string and the next two numbers are not 37 - ignore.
Solution

  • Use the pattern 02(\d{14})37, it will extract a sequence of 14 digits preceded by 02 and followed by 37.