CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaformula
ryanbuckner
ryanbuckner

Reputation: 97

Regexextract decimal number from text with decimals

I have a simple google sheets REGEXEXTRACT function that extracts numbers from NFL lines and over unders.

Here's the problem: The function

=VALUE(REGEXEXTRACT(I3,"-*\d*.?\d+"))

properly extracts -13.5 from text Line: GB -13.5 But when I use the same function on the text O/U: 51.5, it incorrectly extracts 51.0

Where is my regular expression failing me?

Upvotes: 4

Views: 3473

Answers (4)

marikamitsos
marikamitsos

Reputation: 10573

I believe the shortest one would be

=REGEXEXTRACT(I2," .*")+0

enter image description here

Which means:
return everything after the space and turn it to a number

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

I'll throw another approach into the ring:

=1*REGEXEXTRACT(I3,"[\d.-]+")

[\d.-]+ reads "Any digit or period or hyphen in any unbroken combination of one or more such characters."

The 1* is another method of turning a number-like string into a true number.

Upvotes: 3

JvdV
JvdV

Reputation: 75840

What about:

=--REGEXEXTRACT(I3,"-?\d+(?:\.\d+)?")

The pattern means:

  • -? - Optional hypen.
  • \d+ - 1+ digits.
  • (?: - Open non-capture group.
    • \.\d+ - A literal dot and 1+ digitis.
    • )? - Close non-capture group and make it optional.

Instead of VALUE() I used the double negative to make use of the ability of GS to turn a string that looks like a number into a number through direct calculations. The first hyphen creates a negative number which we counter with the 2nd. The same result can be achieved through 1*.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

The problem with your current regex is that, as written, in the second case the leading portion of the pattern is not matching anything, and only the final \d+ matches the integer 51 (see the demo here). You see 51.0 in your Excel spreadsheet because the decimal component defaults to being zero. Please use this regex pattern instead:

-?\d+(?:\.\d+)?

Demo

Upvotes: 6

Related Questions