Regexport() both integers and numbers with decimals

Question

I'm working in Google Sheets and wondering if it's possible to use one regexport() function to export both whole and partial numbers.
I have a column with:

1 Ml/ 2 Ml
2 Ml/ 2.02 Ml
3 Ml/ 4.01 Ml

and want a column with:

2
2.02
4.01

The first value could be 2.00 as well.

I was wondering if this is possible specifically with regular expressions. I know how to do it without. I currently have regexport(cell#, "\/\D(\d+)\D")

Thanks!

Answer 1

I think all you need as a pattern is:

(\d+(?:\.\d+)?) Ml$

---

• ( - 1St apture group.
  • \d+ - One or more digits.
  • (?: - Open non-capture group.
    • \.\d+ - A literal dot followed by one or more digits.
    • )? - Close non-capture group and make it optional.
  • ) - Close 1st capture group.
• Ml$ - Match "Ml" literally upto the end string ancor ($).

---

Add this to an ARRAYFORMULA() like:

=ARRAYFORMULA(REGEXEXTRACT(A1:A3,"(\d+(?:\.\d+)?) Ml$"))