Search code examples
arraysregexgoogle-sheetsgoogle-sheets-formulatruncate

Extract number after dot google sheets


I have this Google sheets example. with a column of digits

Input
37.39850912456523, 176.84092312542114
37.39850912456523, 17.84092312542115
37.39850912456523, 1.84092312542116
37.39850912456523, 176.84092312542117
3.39850912456523, 176.84092312542118
07.39850912456523, 176.84092312542119
3.39850912456523, 176.84092312542120
3.39850912456523, 6.84092312542121

I attempted this formula to get the progress table.

=ArrayFormula(REGEXEXTRACT(A2:A9, "\d{1,3}\.(\d*), \d{1,3}\.(.{5})"))

And when trying to get the 5 digits after the dot on the first capture group with .{5} or \d{5} i get this error.

=ArrayFormula(REGEXEXTRACT(A2:A9, "\d{1,3}\.(.{5}), \d{1,3}\.(.{5})"))

Error
Function REGEXEXTRACT parameter 2 value "\d{1,3}.(.{5}), \d{1,3}.(.{5})" does not match text of Function REGEXEXTRACT parameter 1 value "37.39850912456523, 176.84092312542114".

enter image description here

Progress
39850912456523 84092
39850912456523 84092
39850912456523 84092
39850912456523 84092
39850912456523 84092
39850912456523 84092
39850912456523 84092
39850912456523 84092

What am I missing to get this result with REGEXEXTRACT?

Desired output
398509 840923
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓

And eventually, this final result.

desired results column 1 desired results column 2
37.398509 176.840923
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓
07.398509 ↓↓↓
↓↓↓ ↓↓↓
↓↓↓ ↓↓↓

Solution

  • those look like coordinates so better to TRUNC them:

    =INDEX(IFERROR(1/(1/TRUNC(SPLIT(A1:A, ", "), 6))))
    

    enter image description here

    if you want it regexed try:

    =INDEX(IFERROR(SPLIT(REGEXREPLACE(A1:A, "(\d+.\d{6}).*(, \d+.\d{6}).*", "$1$2"), ",")))
    

    enter image description here


    =INDEX(IFNA(REGEXEXTRACT(A:A, "(\d+.\d{6}).+ (\d+.\d{6})")))
    

    enter image description here

    and multiply by 1 if you need values