Google sheets – Add floats found in single string

February 28, 2024

I have started using financial spreadsheets and am looking for a way to extract float (price) numbers from multi-line strings. Attached is an example of how I format my prices and totals by category:

example of spreadsheet format

As you can see, I am manually inputting the totals found within the parentheses in order to sum them, but it takes a long time and there is margin for error.

I am looking for EITHER an app scripts or formula solution which will extract float numbers–or numbers contained within parentheses–from a single string and total them.

TIA!

I tried using google app scripts with the following method

function addPricesFoundWithinCell(input) {
  var regExp = /(?:\()[^\(\)]*?(?:\))/g;
  var matches = regExp.exec(input);
  const initialValue = 0;
  return matches.reduce(
  (accumulator, currentValue) => accumulator + currentValue,
  initialValue,
);
}

it did not work! I am not so familiar with javascript

>Solution :

You can fix your function like this:

function addPricesFoundWithinCell(input) {
  return (input.match(/\(\d*\.?\d+\)/g) || [])
  .reduce(function(prev, curr) {
    return prev + +curr.replace(/^\(|\)$/g, '');
  }, 0);
}

The $\d*\.?\d+$ regex matches integer or float numbers inside parentheses, these parentheses will be removed later when summing up the numbers, see curr.replace(/^$|$$/g, '').

Alternatively, you can try using matchAll with your regex, but you will need to use Array.from:

function addPricesFoundWithinCell(input) {
  return (Array.from(input.matchAll(/\((\d*\.?\d+)\)/g), x=>x[1]) || [])
  .reduce(function(prev, curr) {
    return prev + +curr;
  }, 0);
}

With Array.from(input.matchAll(/$(\d*\.?\d+)$/g), x=>x[1]), you only get Group 1 content from the matches.