Google-sheets – How to parse an integer in a Google Sheets

google sheets

What is the equivalent of parseInt() (JS) or intval() (PHP) in Google Sheets?

I want to compute the sum of the numbers in a row in a separate cell. So if I had 3 cups and 5 cups in a row, I would get 18.

Best Answer

One way to implement something like this is

=VALUE(INDEX(FILTER(SPLIT(A1, " "), ISNUMBER(SPLIT(A1, " "))); 1; 1))

The result of this formula for some example strings is given below.

enter image description here

You may have to modify this to suit your individual needs. It should work pretty well for strings as simple as "3 cups".

  • SPLIT(..., " ") splits a string into its space-separated parts. You can add more characters to the string, e.g. " -/", to split whenever those characters are found.

  • ISNUMBER checks if a string is a number.

  • FILTER takes the array and filters out everything that doesn't satisfy the second function, i.e., it takes out everything that isn't a number.

  • INDEX(...; 1; 1) returns the value in the first row, first column of the array (so we only get the first number listed, not all the numbers listed).

  • VALUE probably won't be necessary, but just for good measure it converts the final string into a numeric value.

Related Topic