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

Chris Ikei
edited 12/09/19 in Archived 2016 Posts

I'm attempting to filter a column with the following type of data:








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?





  • Dave B
    Dave B ✭✭✭✭✭
    edited 03/13/16



    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.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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 + ""





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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    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?






  • Travis
    Travis Employee

    Try changing your column type to a Contact List column (which will format the contents as text) then filter using Contains. 

  • Travis
    Travis Employee

    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. 

  • Dave B
    Dave B ✭✭✭✭✭

    +1 for filtering partial strings of anything, even forumla results.

This discussion has been closed.