从Google表格中的XML导入中提取REGEXEXTRACT

huangapple go评论59阅读模式
英文:

REGEXEXTRACT out of an XML Import in google sheets

问题

+0",33.89,"4"],["Mar 31 2023 09: +0",34.49,"2&quot]]

+0",4.3,"10&quot]]

+0",6.003,"18"],["Mar 31 2023 09: +0",6.231,"25&quot]]

+0",3.628,"26&quot]]

+0",4.05,"1"],["Mar 31 2023 09: +0",4.63,"13&quot]]

英文:

I imported XML Data out of a script from a website into my google sheets and after some REGEXEXTRACT I now have the following rows:

+0",33.89,"4"],["Mar 31 2023 09: +0",34.49,"2"]]

+0",4.3,"10"]]

+0",6.003,"18"],["Mar 31 2023 09: +0",6.231,"25"]]

+0",3.628,"26"]]

+0",4.05,"1"],["Mar 31 2023 09: +0",4.63,"13"]]

I would like to extract the values that I already marked in bold, so the second to last numerical value out of every row. I tested a bit but can't seem to find something that does exactly what I want, because the rows that I extracted are different in length & composition due to the CONCATENATE function not structuring XML-input from every website the same. Is there a REGEXEXTRACT expression to do what I want?

答案1

得分: 2

=INDEX(IF(LEN(A:A),LET(Σ,"\d+(?:\.\d+)?",REGEXEXTRACT(A:A,",&Σ&),""&Σ&""]]),))

英文:

You may try:

=index(if(len(A:A),let(Σ,"\d+(?:\.\d+)?",regexextract(A:A,",("&Σ&"),"""&Σ&"""]]$")),))

从Google表格中的XML导入中提取REGEXEXTRACT

huangapple
  • 本文由 发表于 2023年3月31日 18:23:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75897436.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定