Filtering

Laura Bell

Can I filter by a number using "contains" as the search criteria?

Andrée Starå

@Laura Bell

You'd need to use the is equal to option instead when it's a number.

Would it work if you converted the 0386 with the VALUE function, so it's 386 instead? If yes, you could use the equal to option for everything.

Make sense?

Andrée Starå

Hi @Laura Bell

I hope you're well and safe!

Yes, probably, but can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Laura Bell

We are trying to filter by Prefix/System. The information was imported from Excel. The prefix-system numbers were derived at from a formula. Some of the values have ' before the number...like the '0384. Others do not. I can filter them using "contains", but only when the apostrophe was used.

Andrée Starå

@Laura Bell

Strange!

It works for me.

Do you mean a filter that you select or in a formula?

' will be added when the number starts with 0, so it's interpreted as text.

Laura Bell

I mean a filter you select. I select the column. Right click on select filter. Then I enter the prefix number in. 0384 shows up when I use the filter "contains" and type in 0384.

This same filter does NOT work when I try it with 1484. Which does not have an apostrophe before the number. I understand that that is just there to keep the zero at the beginning of the number. But it is the only difference that I can see between the two filters.

Andrée Starå

@Laura Bell

You'd need to use the is equal to option instead when it's a number.

Would it work if you converted the 0386 with the VALUE function, so it's 386 instead? If yes, you could use the equal to option for everything.

Make sense?

Laura Bell

Thank you!

Andrée Starå

@Laura Bell

You're more than welcome!