Marie Mosley

Posted on

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.

👍