22

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.

1
  • 4
    Do you mean "3 cups" and "15 cups", or am I missing something?
    – jonsca
    Mar 3, 2014 at 6:08

6 Answers 6

19

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.

4
  • 3
    So the answer is VALUE ... Mar 10, 2015 at 19:40
  • @IulianOnofrei No, VALUE raises an error ("cannot be parsed into a number") on all of the example inputs I gave in the answer. Mar 10, 2015 at 20:51
  • 2
    Sorry, I meant to say that VALUE is the answer to OP's first question and the title too. Mar 10, 2015 at 22:44
  • @IulianOnofrei I don't know JavaScript but that isn't how I understood the OP's question. Mar 11, 2015 at 1:40
3

Another alternative that works with cell containing cup or cups

=ArrayFormula(SUM(INT(LEFT(B1:B2,FIND(" ",B1:B2)))))
2

A parseInt() alternative is:

Formula

=INT()
=SUM(ARRAYFORMULA(INT(LEFT(B1:B2, LEN(B1:B2)-5))))

Screenshot

enter image description here

Note

The LEN(B1:B2)-5, includes a white space.

Reference

https://support.google.com/drive/answer/3093490

0
2

Seems a good use for regex. For example:

WA57716 example

where the formula in E2 is:

="="&join("+",ArrayFormula(regexreplace(B2:D2,"\D","")))

This strips all non numerics, concatenates the result with + and prepends =.

Copy/Paste/Paste values only that into, say, F2 (could be E2) then Replace = with =.

JOIN
REGEXREPLACE

2

Another way is to use RegEx:

=REGEXEXTRACT(A1,"[0-9]+")

Use ; instead of , in countries where , is the decimal seperator (like in Germany).

1
  • This results in a non-number text output that do not work in formula or sorting. ISNUMBER() also returns false on this output Mar 1, 2023 at 23:37
1

User @6005 's answer was very close, except I needed to parse, for example 3km and it wouldn't work. So my solution is just to extract out all the continuous characters that can be part of a number: digit, ., -, and ,:

=VALUE(REGEXEXTRACT(TO_TEXT(A1), "[\d,.-]+"))

Examples:

Note that it is a simple extraction as the regular expression of a valid number with plus and minus sign, thousand separator, decimal, can be quite complicated. See this post on StackOverflow and this post on O'Reilly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.