Excel advanced filter issues with multiple decimal style numbering

Copper Contributor

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? ;)

 

 

1 Reply

@tris_de 

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!