问题描述
我希望 1.2.3.4 能够在Google电子表格中使用ARRAYFORMULA函数内的分割函数。成为 1 , 2 , 3 , 4
这是一行代码(仅适用于1行):
= SPLIT(A2;。)
这就是我想要实现的
//单独
= SPLIT(A2:A;。)
//或带有ARRAYFORMULA
= ARRAYFORMULA(SPLIT(A2:A;。))
由于SPLIT函数不能在ARRAYFORMULA函数中使用,因此我搜索了解决方法():
=> ARRAYFORMULA(IFERROR(REGEXEXTRACT(安培; A2 :A; ^ &安培; REPT(;柱(OFFSET( + [^。] +。 A2 ;;; 1; 4)) - 1)&。+([^。] +))))
除了它没有正确分割的事实外,它几乎可以工作,这是我得到的结果:
1.2.3.4变成了4 ,4,4,4
如果我能找到解决方法来实际正常工作或更好的选择。 。
您的表达式创建如下正则表达式:
<$ p $ (+)[+] [+] [+] [+] [+] [+] [+]
。+ 部分将尽可能匹配任何内容,从而推动他持续<$
如果您使用类似这样的表达式,它应该更好地工作:
^ \。[^。] * \。[^。] * \。[^。] * \。([^。] *)
它会一直统计每个 。,并且绝不会将最后一个组推得太远。
<您可以使用 {3} $ c $ b>而不是重复子模式,但这会改变你的原始公式太多)
以下是相应的公式:
= ARRAYFORMULA(IFERROR(REGEXEXTRACT(。& A2:A;
^& REPT(\。[^。] *; COLUMN(OFFSET(A2 ;;; 1; 4)) - 1)&\。([^。] * ))))
I am trying to use the split function within the ARRAYFORMULA function inside a google spreadsheet.
I want "1.2.3.4" to become "1", "2", "3", "4"
This is the code for a one-liner (working for 1 row only):
=SPLIT(A2;".")
This is what I want to achieve (to work on multiple rows at the same time):
//alone =SPLIT(A2:A;".") //or with ARRAYFORMULA =ARRAYFORMULA(SPLIT(A2:A;"."))
Since the SPLIT function can't be used within the ARRAYFORMULA function, I searched for a workaround (ARRAYFORMULA() does not work with SPLIT()):
=ARRAYFORMULA(IFERROR(REGEXEXTRACT("."&A2:A;"^"&REPT(".+[^.]+";COLUMN(OFFSET(A2;;;1;4))-1)&".+([^.]+)")))
It's almost working except for the fact that it doesnt split correctly, here is the result I get:
"1.2.3.4" becomes "4", "4", "4", "4"
If I can get the workaround to actually work correctly or a better alternative that would be more than awesome...
Your expression creates regular expressions like this:
^.+[^.]+.+[^.]+.+[^.]+.+([^.]+)
The .+ part will match anything, as much as possible, thus pushing he last ([^.]+) to the end.
If you use an expression like this instead, it should work better:
^\.[^.]*\.[^.]*\.[^.]*\.([^.]*)
It will always count each ., and never push the last group too far.
(You could use {3} instead of repeating the subpattern, but that would change your original formula too much)
Here is the corresponding formula:
=ARRAYFORMULA(IFERROR(REGEXEXTRACT("."&A2:A; "^"&REPT("\.[^.]*";COLUMN(OFFSET(A2;;;1;4))-1)&"\.([^.]*)")))
这篇关于试图在Google电子表格中的arrayformula内分割的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!