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 |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
| ↓↓↓ | ↓↓↓ |
CodePudding user response：
As far as I understand RegexExtract returns the matching text if no group is defined, or the first group if one is defined. So, I defined a group for the first five digits and discarded the rest
=RegexExtract(A1, "\d*\.(\d{1,6})")
=RegexExtract(A1, "\d*\.\d*, \d{1,3}\.(\d{1,5})\d*")
CodePudding user response：
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"), ",")))
CodePudding user response：
The split() function will automatically coerce values to numbers when possible. To get the final desired result you show, use round(), like this:
=arrayformula( trunc( split(A2:A9, ", ", false, true), 6 ) )