## DEV Community is a community of 639,914 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

# Recreating Excel’s ISNUMBER in a Numbers Spreadsheet

The other day I had to go through a bunch of strings in a .csv file and find which ones ended with two digits.

They were all short strings created from a limited character set that included all the letters of the English alphabet, numbers 0-9, dashes, and underscores. Like:

• Rolf01
• layout_final_final_final
• Sally06
• Marius48
• michelle-78
• Heather78
• group-in-august

I opened the file in Numbers for Mac, fully planning to use `ISNUMBER` to find if the last two characters of each string were numbers. Excel and Google Sheets both have that function. But as it turns out, Numbers doesn't 😬

### The Formula and the Filter

After a little digging in the Numbers docs I came up with an alternative.

I put together this formula, which I added to column B.

`NOT(ISERROR(VALUE(RIGHT(A2,”2”))))`

This printed `TRUE` in the cell if the last two characters were numbers, or `FALSE` if the last two characters were not numbers.

Then, using Numbers’ “Organize” feature, I filtered the sheet by Column B, where the `text is not` "FALSE".

That filtered out all of the rows where the strings in column `A` didn’t end in two digits.

### How the Formula Works

This formula is a series of questions about the strings in column `A`. The questions start in the innermost function and works outward.

My first question is: what are the last 2 characters of the string in column A? As a function that’s:

`RIGHT(A2,”2”)`

This function returns a new string made up of the two characters at the right end of the string in cell `A2`. That’s another way of saying the “last two” characters in a string read left to right.

Once I had those two characters, the question I ask with the next two steps of the function is “are those two characters numbers?”

`VALUE(RIGHT(A2,”2”))`

`VALUE` returns the “number value” of a string. It’s handy for things like getting just the number from a price string that includes a currency symbol. For example, it would return `100` from a string that read `\$100`.

When the string does not have a number value—for example, because it’s a string of letters—the `VALUE` function will throw an error.

So at this point in the function, I’m using it to return the numeric value of the last 2 characters of the string in column `A`.

If I stopped here, I would get numbers in the cells where the string had a number value, and errors in the cells where the strings did not have a number value.

That’s technically enough to sort the sheet — I could filter out all rows where column B’s value is not within the range of 0 - 99. But it's messy.

To keep things tidy, I added a check for if the `VALUE` function returned an error.

`ISERROR(VALUE(RIGHT(A2,”2”)))`

`ISERROR` returns `TRUE` if the expression inside it returns an error, or `FALSE` if the expression inside does not return an error.

Here, I’m asking it to check if the numeric `VALUE` of the two characters at the `RIGHT` end of the string in `A2` returns an error. If it does, the question `ISERROR` is answered with `TRUE`.

That answers my question about whether or not the last two characters are numbers: `TRUE` means there was an error with getting the number value of the last two characters, so they are not numbers. `FALSE` means there was no problem with getting their number value, so they are numbers.

And, it’s enough of an answer to organize my sheet. I could filter out the rows where this formula returned `TRUE` .

That’s a nice short formula that gave the answer I needed, but I felt that formula brevity got in the way of spreadsheet clarity here.

### Saying Yes with TRUE

Since the question I’m asking this sheet is “does this string end with two digits?”, Answering ”yes” by returning `TRUE` feels better than answering “yes” with `FALSE`.

And that’s where the last piece of the formula comes in: the `NOT` function.

`NOT(ISERROR(VALUE(RIGHT(A2,”2”))))`

Here, I’m asking the formula to say `TRUE` when there is `NOT` an error in the numeric `VALUE` of the `2` `RIGHT`-most characters of the string in cell `A2`.

This gave me `TRUE` whenever the string ended with two digits, and `FALSE` when it didn’t.

Then when I organized the sheet to filter out all the rows where the answer was `FALSE`, I got all the rows where the string in column `A` ended with two digits.

👍