Regexextract decimal number from text with decimals

Question

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?

If you are willing to change your formula you could probably also use [this very short version](https://stackoverflow.com/a/65782427/1527780). — marikamitsos, Jan 18 '21 at 21:44

Tim Biegeleisen · Accepted Answer · 2021-01-18T15:47:08.673

6

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

edited Jan 18 '21 at 15:47

answered Jan 18 '21 at 15:43

Tim Biegeleisen

502,043
27
286
360

This returned a value of .5 – ryanbuckner Jan 18 '21 at 15:46
`REGEXEXTRACT()` will return the 1st capture group if you don't make it non-capturing. – JvdV Jan 18 '21 at 15:46
@JvdV I intentionally left out `?:` because I assumed Excel's regex engine to be primitive and not even understand it. I guess I was mistaken. – Tim Biegeleisen Jan 18 '21 at 15:47
OP is using Google Sheets which is a little less primitive =) – JvdV Jan 18 '21 at 15:48

score 3 · Answer 2 · answered Jan 18 '21 at 17:31

3

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.

answered Jan 18 '21 at 17:31

Erik Tyler

9,079
2
7
11

My preferred solution. For added compatibility use [\d.,-]+ to include , separated thousands. wrap in VALUE() to convert properly to a number – babelmonk Dec 30 '22 at 07:37

JvdV · Answer 3 · 2021-01-18T16:39:04.703

1

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*.

edited Jan 18 '21 at 16:39

answered Jan 18 '21 at 15:43

JvdV

70,606
8
39
70

This worked!! Can you explain the -- in the formula ? – ryanbuckner Jan 18 '21 at 15:49
1

I'm learning a lot from this. Did you mean to put a question mark on the end of your pattern to make the decimal part optional though? It looks like it doesn't work on integers as shown, should they ever occur. – Tom Sharpe Jan 18 '21 at 16:36
I edited the pattern as indeed it needed the question mark. Strange how I had it all ready included in my explanation underneath it. @TomSharpe. And the whole point was to have the optional decimal point in case of integers as you suggest. Thank for the extra eyes here. – JvdV Jan 18 '21 at 16:40

score 0 · Answer 4 · answered Jan 18 '21 at 21:39

0

I believe the shortest one would be

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

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

answered Jan 18 '21 at 21:39

marikamitsos

10,264
20
26

whoa, this is fantastic! – ryanbuckner Jan 19 '21 at 19:14

Regexextract decimal number from text with decimals

4 Answers4

Demo