试图在Google电子表格中的arrayformula内分割

试图在Google电子表格中的arrayformula内分割

本文介绍了试图在Google电子表格中的arrayformula内分割的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



我希望 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} 而不是重复子模式,但这会改变你的原始公式太多)



以下是相应的公式:

  = 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内分割的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:29