Retrieving numerical values from a string in Google Spreadsheet

I need to extract numeric values from the cell, but it fails to do so when there is only one digit present. Consequently, I require the extraction of digits exclusively into a new cell, followed by their summation.


Question:

I have been grappling with this issue for quite some time. My objective is to retrieve solely the numerical values from the cell. However, for an unknown reason, it fails to extract a single digit. Further information is provided below.

String End Result
$8 – added to cart 8

Formula used:

=REGEXEXTRACT(J92,"d+.?d+")

The formula is able to capture digits that include decimals. Strangely enough, this formula proves effective when applied to the string “$81 – added to cart”. Could someone assist me in modifying it so that it functions properly on strings containing only one digit?


Solution 1:

Use

=REGEXEXTRACT(J92,"d+(?:.d+)?")


EXPLANATION

--------------------------------------------------------------------------------
  d+                      digits (0-9) (1 or more times (matching
                           the most amount possible))
--------------------------------------------------------------------------------
  (?:                      group, but do not capture (optional
                           (matching the most amount possible)):
--------------------------------------------------------------------------------
    .                       '.'
--------------------------------------------------------------------------------
    d+                      digits (0-9) (1 or more times (matching
                             the most amount possible))
--------------------------------------------------------------------------------
  )?                       end of grouping

The reason your regex is not functioning is due to its meaning.

--------------------------------------------------------------------------------
  d+                      digits (0-9) (1 or more times (matching
                           the most amount possible))
--------------------------------------------------------------------------------
  .?                       any character except n (optional
                           (matching the most amount possible))
--------------------------------------------------------------------------------
  d+                      digits (0-9) (1 or more times (matching
                           the most amount possible))


Solution 2:

try:

=REGEXMATCH(A1&""; "d+(?:.d+)?")*1

for array use:

=INDEX(IFNA(REGEXEXTRACT(A2:A&""; "d+(?:.d+)?")*1))


Solution 3:


This will meet your expectations.


=IFERROR(REGEXEXTRACT(J92,"[d\.,]+")*1)

Please take note that in the given text, both decimal points and commas have been included. After that, the extracted string is multiplied by 1 in order to convert it into a real value. If the source string does not have any matches, the result of
IFERROR
will be null.

Please be aware that manually processing each cell is not an efficient method when an array formula can process the entire range. If you are open to using this approach, kindly provide a link to the spreadsheet (or a copy of it) below.


Solution 4:

The provided regular expression searches for a sequence of digits, followed by optional additional elements, and ends with another sequence of digits.
There are multiple regular expressions available for your task. I suggest utilizing the following pattern: “d+(.d+)?

Frequently Asked Questions