17

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.

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

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.

| improve this answer | |
  • 2
    So the answer is VALUE ... – Iulian Onofrei Mar 10 '15 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. – 6005 Mar 10 '15 at 20:51
  • 1
    Sorry, I meant to say that VALUE is the answer to OP's first question and the title too. – Iulian Onofrei Mar 10 '15 at 22:44
  • @IulianOnofrei I don't know JavaScript but that isn't how I understood the OP's question. – 6005 Mar 11 '15 at 1:40
3

Another alternative that works with cell containing cup or cups

=ArrayFormula(SUM(INT(LEFT(B1:B2,FIND(" ",B1:B2)))))
| improve this answer | |
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

| improve this answer | |
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

| improve this answer | |
1

Another way is to use RegEx:

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

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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