Recreating Excel’s ISNUMBER in a Numbers Spreadsheet
Tags: spreadsheets, csv, numbers
Published: 2019-06-02 17:45:13

---
title: Recreating Excel’s ISNUMBER in a Numbers Spreadsheet
published: true
description: on writing a Numbers formula to sort the sheet by rows where the last two characters of a string are digits. 
tags: spreadsheets, csv, numbers
---

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
- menu-v2
- Sally06
- Marius48
- header_large_v2
- 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](https://www.apple.com/mac/numbers/compatibility/functions.html) I came up with an alternative.

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

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

![Numbers sheet with the function shown in the function editor.](https://thepracticaldev.s3.amazonaws.com/i/edmstwcgwl7q011jramw.png)

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

![Numbers sheet showing TRUE when column A strings end in two digits, or FALSE when they do not.](https://thepracticaldev.s3.amazonaws.com/i/kk3uo3o0fzvgb9a1lhun.png)



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

![Numbers sheet organized so that only the rows with TRUE are shown.](https://thepracticaldev.s3.amazonaws.com/i/ah0k6kw6fc0hxuvifref.png)

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. 

![The last two characters of the string in column A is shown in column B.](https://thepracticaldev.s3.amazonaws.com/i/6y08e9zp2ij0ti9e2xj3.png)

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.  

![Errors shown when the VALUE function can't find a numeric value.](https://thepracticaldev.s3.amazonaws.com/i/yvqywbjwmhngm1z85l1y.png)

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. 

![using the ISERROR function on the sheet.](https://thepracticaldev.s3.amazonaws.com/i/dorhtplyetta6qr909ax.png)

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. 

![Final formula being entered into the sheet.](https://thepracticaldev.s3.amazonaws.com/i/heb3uiq55upq9e3j56my.png)

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. 

![Fully sorted sheet, showing only the rows where the string in column A ends with 2 digits.](https://thepracticaldev.s3.amazonaws.com/i/jtgils07vrvissgm6l30.png)

👍