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.

👍

## Top comments (0)