Feb 20 2019 02:41 PM
Howdy Folks,
Currently running MS Excel professional 2019 and currently feel blocked by the advanced filter function.
In column A, I have the title "Number" and in column B, the title "Description".
Column A is formatted as text and contains the following style number system:
1
1.1
1.2
1.2.1.1
1.2.1.2
...2.1.28
1.2.2
2
3
etc. etc.
I basically then want to filter by column A, down to a certain level of detail. This level is included in the number format. As an example, I just want to see the top level - Ok, this is relatively straight forward with the filter as text option and would return just whole integer numbers 1 2 3 etc.
I can add a second argument in the filter as text option, which gives me the start of the next level of detail e.g. =?.?
However, when I want to filter any more than two levels (e.g. 1 & 1.1 1.2 1.3 etc) then I include a table with the syntax which I wish to filter. My table would read:
"Number" in the header of column A, and then I would include operators like:
="=?" [Top level]
="=?.?" [2nd level]
="=?.??" [2nd level]
="=?.?.?" [3rd level]
="=?.??.?" …. and so on.
The filter is not coping with returning the correct values, I assumed because it is still trying to convert the text back into a number, due to the single decimal. I therefore swapped the decimal sign for an accent " ` ", but this has not helped.
Any other ideas how to filter multiple syntaxes of text would be very much appreciated? ;)
Oct 25 2019 08:26 AM
For the advanced filter to group by level, just write the following formula in the Criteria Range:
= TEXT ("1 *"; "General")
The 1 * represents everything that follows next to what is to the right of the "1".
The "General" is not essential but makes you sure the comparison is against a text.
Hope this helps!