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

Find more posts tagged with

Sheets

Comments

Dave B

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.

J. Craig Williams

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

Jenny Lam

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

Jeff K.

Have you tried adding an asterisk before and after the number for your Contain field?

Ex: *7789*

Chris Ikei

Tried the asterisk. Doesn't work. Looks like I'll need to use the apostrophe prefix method. Bla.

Thanks everyone.

J. Craig Williams

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

Travis

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

Travis

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.