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:
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 😬
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.
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.
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:
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 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
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
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.
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
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
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.
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
And that’s where the last piece of the formula comes in: the
Here, I’m asking the formula to say
TRUE when there is
NOT an error in the numeric
VALUE of the
RIGHT-most characters of the string in cell
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.