filter not working

i have a Text column that is imported from a huge file, filtered down. i can not change it.

System column contains 123456z01, 123456n02, 123456.

i use the filter contain but it will not show "123456".

the only thing i can think of is that is is a number in a text field.

i looked at using contains in the filter and selection all that start with "123456", but some may have 15-20 lines to check.

is there another way that i can get the Contains "123456" to work?

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Stephen Hindes

You're right, the Filter is unable to identify the selection of numbers from within the text in that column.

If this is the only number selection you're looking for, I would set up a Helper Checkbox column. Then you can use CONTAINS in a formula which will be able to read through the text for the numbers 123456, like so:

=IF(CONTAINS(123456, [Column Name]@row), 1, 0)

Screen Shot 2021-11-23 at 12.44.34 PM.png

You can apply this as a Column Formula and then filter based on if the box is checked or not.

The other way to find all the matching cells would be to use Find in Smartsheet. This will create a blue border around the first cell, then when you hit "next" it will take you to the next cell with those numbers:

Screen Shot 2021-11-23 at 12.42.38 PM.png

Cheers,

Genevieve

Stephen Hindes

let me give more detail, i import this file from an excell sheet. it has about 900-1500 lines. the system numbers (123456) are different. some are number only and some are text (123456Z01). some of the system numbers have 5 numbers, some have 6 or 7. In excell i could do a macro to add apostrophe to the beginning, but when i copy into SS, it still copies as a number not a text.

is there a formula were i could pull out the numbers only, excluding the character and what is after it? Remember the number could be 5, 6 or 7 characters long.

Genevieve P.

Hi @Stephen Hindes

Thank you for the clarification! Yes, we can use a formula to extract the 7, 6, or 5 numbers from the left of the text:

=IFERROR(VALUE(LEFT([System Numbers]@row, 7)), IFERROR(VALUE(LEFT([System Numbers]@row, 6)), VALUE(LEFT([System Numbers]@row, 5))))

This looks to turn the 7 left characters into a Value (number), but if it errors, it tries the 6 left characters, and if that errors it tries the 5, like so:

Screen Shot 2021-11-25 at 12.39.06 PM.png

You can turn this into a Column Formula. This way it will only grab the 5 numbers if there isn't 6 or 7, so it doesn't cut anything short. Then you can use the helper column with the Number to filter by!

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.