# How do I parse an integer in a 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`.

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

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.

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.

• 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
• 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

Another alternative that works with cell containing cup or cups

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

A `parseInt()` alternative is:

## Formula

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

## Note

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

## Reference

Seems a good use for regex. For 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 `=`.

Another way is to use RegEx:

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

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

• 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

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.