Sign in to join the conversation:
I have a bunch of numbers in a column, I want to find all the rows that include the # 13 in the number. The "contains" filter option does not work. Need suggestions on finding certain subsets of a longer number on the fly.
Hi Bill,
If these are purely numeric values (no letters or other symbols mixed in), there isn't a way to filter down or quickly find every number that has a 13 in it. As you've found, the contains criteria currently doesn't work with numeric values, only text values. If your numbers have letters/characters mixed with them, then "contains" will be able to find text values that contain the number.
Unless you're able to convert your numbers to text (add an apostrophe to the beginning, or dashes if you're looking through serial/parts numbers in your sheet), then there isn't an easy way in Smartsheet to filter/search for that number.
You may need to consider exporting to Excel when you need to find numbers that have a 13 in them. More on exporting is available here: https://help.smartsheet.com/articles/770623-exporting-sheets-reports-from-smartsheet
Extrapolating from Shaine's post, create a new column.
In that column, type
=[NumberColumn]1 + ""
where [NumberColumn] is the name of the column and row number is 1.
Copy that formula to the rest of the new column.
This will convert your numbers to text.
You can then search / filter on them.
Watch out for weird results. I ran into a bug with long integers using the formula over the weekend and was told, eventually, that it is a known issue.
Craig
Hello, I'm working through the writing formulas optional activity and in the 'Nested IF function - Progress Column' task it directs you to put in the following formula '=IF([% Complete]@row = 0, “Empty”)'. However, when I put this formula into the spreadsheet, '04a_Marketing Request Tracker - Formulas', I get an…
I have an automation that has a condition where I am looking at the column "LEA Interco Supporting Site(s)" and trying to find columns that have the value "Fort Loramie" in them. This works well when "Fort Loramie" is the first item listed in the cell, but it doesn't work when "Fort Loramie" is the second or third value in…
Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026. I have done it on Excel sheets manually, but I think there should be a way to do it with…
