Google Sheets Regexreplace用于将年份添加到月份

Question

我有一个这样的数据集：

2019-01-02-03-04-05-06-07-08-09-10-11-12
2020-01-02-03-04-05-06-07-08-09-10-11-12
2021-01-02-03-04-05-06-07-08-09-10-11-12
2022-01-02-03-04-05-06-07-08-09-10-11-12
2023-01-02-03-04-05-06-07-08-09-10-11-12
2024-01-02-03-04-05-06-07-08-09-10-11-12

每一行的所有内容都在一个sing列中。我想要应用一个regexreplace()来得到如下结果：

2019-01,2019-02,2019-03,2019-04,2019-05,2019-06,2019-07,2019-08,2019-09,2019-10,2019-11,2019-12
2020-01,2020-02,2020-03,2020-04,2020-05,2020-06,2020-07,2020-08,2020-09,2020-10,2020-11,2020-12
2021-01,2021-02,2021-03,2021-04,2021-05,2021-06,2021-07,2021-08,2021-09,2021-10,2021-11,2021-12
2022-01,2022-02,2022-03,2022-04,2022-05,2022-06,2022-07,2022-08,2022-09,2022-10,2022-11,2022-12
2023-01,2023-02,2023-03,2023-04,2023-05,2023-06,2023-07,2023-08,2023-09,2023-10,2023-11,2023-12
2024-01,2024-02,2024-03,2024-04,2024-05,2024-06,2024-07,2024-08,2024-09,2024-10,2024-11,2024-12

这基本上就是用对应行中的前4个数字替换每个"-"。

因为我知道第一部分中的年份和第二部分中的月份，我知道我可以使用以下公式来获得预期的结果：

=regexreplace(A1,"^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})-([0-9]{1,2})","$1-$2,$1-$3,$1-$4,$1-$5,$1-$6,$1-$7,$1-$8,$1-$9,$1-$10,$1-$11,$1-$12,$1-$13")

附言:我的数据是A1格式的

但是如何拥有更动态的东西，用同一字符串的一部分替换字符串的几个部分呢？

Answer 1

你真的想要REGEXREPLACE吗？或者：

=ARRAYFORMULA(TEXTJOIN(",",TRUE,LEFT(A1,5)&SPLIT(MID(A1,6,LEN(A1)),"-")))

使用REGEXREPLACE，可能是这样的：

=LEFT(A1,7)&REGEXREPLACE(A1,"(\d{4}-\d{2})?(-)",","&LEFT(A1,5))

Answer 2

尝试：

=ARRAYFORMULA(IFERROR(TEXT(INDEX(SPLIT(A1:A, "-"),,1)&"-"&
 TRANSPOSE(QUERY(TRANSPOSE(SPLIT(A1:A, "-")), "offset 1", 0)), "yyyy-mm")))

​

​

然后：

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 TEXT(INDEX(SPLIT(A1:A, "-"),,1)&"-"&
 TRANSPOSE(QUERY(TRANSPOSE(SPLIT(A1:A, "-")), "offset 1", 0)), 
 "yyyy-mm")&",")),,999^99)), ",$", ))

​

​

或者不带空格：

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
 TEXT(INDEX(SPLIT(A1:A, "-"),,1)&"-"&
 TRANSPOSE(QUERY(TRANSPOSE(SPLIT(A1:A, "-")), "offset 1", 0)), 
 "yyyy-mm")&",")),,999^99)), " |,$", ))