这个修改后的公式怎么样?
修改后的公式:
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A1:D),"(([\w\s]+,){3}[\w\s]+)","$1@"),"@")),",")))
- The flow of this formula is as follows.
- 通过忽略空单元格来连接所有单元格值
TEXTJOIN
.
- Put
@
使用 4 列的连接文本值REGEXREPLACE
.
- 将文本值拆分为
@
using SPLIT
.
- 使用转置分割值
TRANSPOSE
.
- 将每一行拆分为
,
using SPLIT
.
Result:
![enter image description here](https://i.stack.imgur.com/MzToH.png)
Note:
- If
,
and @
包含在单元格值中,请在公式中更改它们。
参考:
- TEXTJOIN
- 正则表达式替换
- SPLIT
- 移调
Added 1:
About I guess this will fail, if cell values are more than 1 character.
of 大师的评论,我测试如下。
![enter image description here](https://i.stack.imgur.com/NmMru.png)
Added 2:
对于OP的新情况,我添加了以下修改后的公式。在这种情况下,正则表达式被修改。
修改后的公式:
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(REGEXREPLACE(TEXTJOIN(",",TRUE,A1:D),"(([\w\s\S]+?,){4})","$1@"),"@")),",")))
Result:
![enter image description here](https://i.stack.imgur.com/ttZfc.png)