Forum Discussion
Filter with numbers and symbols
You say you have 4 types of data but not sure what those are. I see blank, numbers, number ranges (x-x), number+, number- (for an entry like 40- I assume it is basically 0-40 assuming 0 is the minimum)
You don't say what kind of filtering you want. but I'll assume some sort of number range. because if you just want to filter for any row that has a particular value in it then you can use the text filter.
So my best suggestion is to create 2 helper columns for Min WA and Max WA and then filter on those columns. Formulas for those columns might be:
Min WA: =IFS([WA]="","",ISNUMBER([WA]),[WA],RIGHT([WA])="+",--LEFT([WA],LEN([WA])-1),RIGHT([WA])="-",0,1,--LEFT([WA],FIND("-",[WA])-1))
Max WA: =IFS([WA]="","",ISNUMBER([WA]),[WA],RIGHT([WA])="-",--LEFT([WA],LEN([WA])-1),RIGHT([WA])="+",100,1,--RIGHT([WA],LEN([WA])-FIND("-",[WA])))
sry for the edit but had a typo and also wanted to mention this assumes a min of 0 and max of 100 but you could easily substitute sufficiently small and large numbers instead (e.g. 1e-99 and 1e99)
Thanks for your reply.
Sorry I was not clear enough. I want to be able to filter from high to low. Also I was talking about column F (WA). So entries like 40- are not relevant. If you mean this symbol by blank: · Then you are correct. That is the first data entry. The other 3 are indeed: numbers, number ranges (x-x), number+.
Also maybe I should add. That formula looks very complicated to me. Can I just copy paste that in the 2nd cell of the new inserted column G?