REGEXEXTRACT out of an XML Import in google sheets

Question

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?

Answer 1

You may try:

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