How to extract numbers with multiple decimal points from text in Google Sheets?

user_6879038 0
Viewed 440

I'm trying to extract a software version (pure numbers and decimals) from a text string in a cell, but because it has multiple decimals places I can't get the full result.

Examples (Input --> Output):

Plugin Version v4.5.2 Available --> 4.5.2
New Plugin v1.15.49 Available --> 1.15.49

So far I'm working with this formula, but it only gives me the first decimal result, it can't handle 2 decimals because these are software version numbers, not real numbers.

=REGEXEXTRACT(A1,"-*\d*\.?\d+")
google-sheets decimal regex
3 Answers

You can also try

=regexextract(A1; "[0-9.]+")
user_38894230

Try like:

=REGEXEXTRACT(A1;"(-*\d*[\.?\d+]+)")

Explanation: The original:

-*\d*\.?\d+

matches:

  • -*: 0 to n - characters followed by:
  • \d*: 0 to n decimal characters (0-9), followed by:
  • \.?: 0 to 1 . character(s) (it has to be escaped, otherwise it means "any character"), followed by:
  • \d+: 1 to n decimal characters.

We now:

  • wrap \.?\d+ into a "selection" ([...])
  • and match 1 to n(+) of its occurences: [\.?\d+]+
  • additionally(not mandatory) enclose all in a "capturing group" ((...)) ...we could also: extract parts of it.
user_5923550

If you just want the number after v,

=REGEXEXTRACT(A1,"v([\d\.]+)")
  • \d any digit
  • \. literal .
  • []+ match one or more of any of the characters inside []
user_84044530
Related