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".
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 | ↓↓↓ |
↓↓↓ | ↓↓↓ |
↓↓↓ | ↓↓↓ |
those look like coordinates so better to TRUNC them:
=INDEX(IFERROR(1/(1/TRUNC(SPLIT(A1:A, ", "), 6))))
if you want it regexed try:
=INDEX(IFERROR(SPLIT(REGEXREPLACE(A1:A, "(\d+.\d{6}).*(, \d+.\d{6}).*", "$1$2"), ",")))
=INDEX(IFNA(REGEXEXTRACT(A:A, "(\d+.\d{6}).+ (\d+.\d{6})")))
and multiply by 1 if you need values