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

👍