Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Using "Contains" for Numbers
I'm attempting to filter a column with the following type of data:
10007789001
10007796003
10007789001
10007789002
10007801001
I tried to filter this column using "Contains" 7789. I expected every row with 10007789001 and 10007789002 would be shown. However, Smartsheet indicated that 0 rows met filter criteria. This doesn't sound like proper functionality. Can anyone assist?
Thanks
Comments
-
Chris,
I've seen some odd behavior like this as well. It doesn't seem to fail in all cases. I have several sheets where it works and some that don't. I can't figure out what makes it work or fail. Perhaps Travis will chime in and let us know.
Things I've tested:
Hand-typed numbers vs. linked vs. pasted vs. formula result.
Complete number strings vs. partial strings (as in your example)
Partial strings that start the string, are in the middle, or at the end
Alpha-numeric combinations
Again, in some sheets all of the above either work or fail and I cannot determine what causes it.
-
Chris,
If the system thinks that the information is a number, then you can't filter (I believe) on the digits in a number.
Without knowing your column structure, I would suggest adding a column and forcing the system to recognize the value as text and try the filter there.
=[Current Column]23 + ""
Craig
-
If you prefer to format a number as text - just place an apostrophe in front of the number to preserve it in its entirety.
Reference: http://help.smartsheet.com/customer/portal/articles/504619#text
-
Have you tried adding an asterisk before and after the number for your Contain field?
Ex: *7789*
-
Tried the asterisk. Doesn't work. Looks like I'll need to use the apostrophe prefix method. Bla.
Thanks everyone.
-
Travis,
Feature request to solve this:
Allow formatting of all numbers as text (in a column)
Allowing In a cell would lead to too many bug reports, I would imagine.
Only for Text/Number columns.
How would that prevent incrementing or other math formulas in the cell?
Hmm.
Craig
-
Try changing your column type to a Contact List column (which will format the contents as text) then filter using Contains.
-
Added your vote, Craig!
My suggestion above would be a workaround to your suggestion of a text only column. The Contact list will format its content as text but because this is text, the values will not work in a numeric formula unless you convert the value to a number using the VALUE() function.
-
+1 for filtering partial strings of anything, even forumla results.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives