Forum Discussion
Filter with numbers and symbols
Dear community,
In my Excel worksheet I have 4 types of data input. But I am not able to filter it. That probably is because Excel doesn't count +, - and · as numbers. How can filter nevertheless?
Here is a GIF to make clear what I am talking about.
https://gyazo.com/1b5fca23f105d5cf62d936e9f3bc6b00
I appreciate your help and effort.
3 Replies
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?Kalthof So I don't even know what filter high to low would necessarily look like, but with both a MIN WA and MAX WA column you have a lot of options (e.g. sort MAX WA column 1st and MIN WA column 2nd or is it a maximum minimum value or max value then name ...)
As for the formulas the [WA] needs to be replaced with either [@WA] if that is a table or F2 if not
As for what the formulas do here is the Min explained:
Min WA:
=IFS( F2="","", 'if blank return blank
ISNUMBER(F2,F2, 'if a number return the number
RIGHT(F2)="+",--LEFT(F2,LEN(F2)-1), 'if right character is "+" then return all but that last character
RIGHT(F2)="-",0, 'if right character is "-" then return 0
1,--LEFT(F2,FIND("-",F2)-1)) 'all other cases return what is before the "-"
