Excel advanced filter issues with multiple decimal style numbering

%3CLINGO-SUB%20id%3D%22lingo-sub-354292%22%20slang%3D%22en-US%22%3EExcel%20advanced%20filter%20issues%20with%20multiple%20decimal%20style%20numbering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354292%22%20slang%3D%22en-US%22%3E%3CP%3EHowdy%20Folks%2C%3C%2FP%3E%3CP%3ECurrently%20running%20MS%20Excel%20professional%202019%20and%20currently%20feel%20blocked%20by%20the%20advanced%20filter%20function.%3C%2FP%3E%3CP%3EIn%20column%20A%2C%20I%20have%20the%20title%20%22Number%22%20and%20in%20column%20B%2C%20the%20title%20%22Description%22.%3C%2FP%3E%3CP%3EColumn%20A%20is%20formatted%20as%20text%20and%20contains%20the%20following%20style%20number%20system%3A%3C%2FP%3E%3CP%3E1%3C%2FP%3E%3CP%3E1.1%3C%2FP%3E%3CP%3E1.2%3C%2FP%3E%3CP%3E1.2.1.1%3C%2FP%3E%3CP%3E1.2.1.2%3C%2FP%3E%3CP%3E...2.1.28%3C%2FP%3E%3CP%3E1.2.2%3C%2FP%3E%3CP%3E2%3C%2FP%3E%3CP%3E3%3C%2FP%3E%3CP%3Eetc.%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20basically%20then%20want%20to%20filter%20by%20column%20A%2C%20down%20to%20a%20certain%20level%20of%20detail.%20This%20level%20is%20included%20in%20the%20number%20format.%20As%20an%20example%2C%20I%20just%20want%20to%20see%20the%20top%20level%20-%20Ok%2C%20this%20is%20relatively%20straight%20forward%20with%20the%20filter%20as%20text%20option%20and%20would%20return%20just%20whole%20integer%20numbers%201%202%203%20etc.%3C%2FP%3E%3CP%3EI%20can%20add%20a%20second%20argument%20in%20the%20filter%20as%20text%20option%2C%20which%20gives%20me%20the%20start%20of%20the%20next%20level%20of%20detail%20e.g.%20%3D%3F.%3F%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20want%20to%20filter%20any%20more%20than%20two%20levels%20(e.g.%201%26nbsp%3B%20%26amp%3B%26nbsp%3B%201.1%20%26nbsp%3B%201.2%20%26nbsp%3B%26nbsp%3B%201.3%20etc)%20then%20I%20include%20a%20table%20with%20the%20syntax%20which%20I%20wish%20to%20filter.%20My%20table%20would%20read%3A%3C%2FP%3E%3CP%3E%22Number%22%20in%20the%20header%20of%20column%20A%2C%20and%20then%20I%20would%20include%20operators%20like%3A%3C%2FP%3E%3CP%3E%3D%22%3D%3F%22%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%5BTop%20level%5D%3C%2FP%3E%3CP%3E%3D%22%3D%3F.%3F%22%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%5B2nd%20level%5D%3C%2FP%3E%3CP%3E%3D%22%3D%3F.%3F%3F%22%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20%5B2nd%20level%5D%3C%2FP%3E%3CP%3E%3D%22%3D%3F.%3F.%3F%22%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%5B3rd%20level%5D%3C%2FP%3E%3CP%3E%3D%22%3D%3F.%3F%3F.%3F%22%20%E2%80%A6.%20and%20so%20on.%3C%2FP%3E%3CP%3EThe%20filter%20is%20not%20coping%20with%20returning%20the%20correct%20values%2C%20I%20assumed%20because%20it%20is%20still%20trying%20to%20convert%20the%20text%20back%20into%20a%20number%2C%20due%20to%20the%20single%20decimal.%20I%20therefore%20swapped%20the%20decimal%20sign%20for%20an%20accent%20%22%20%60%20%22%2C%20but%20this%20has%20not%20helped.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20ideas%20how%20to%20filter%20multiple%20syntaxes%20of%20text%20would%20be%20very%20much%20appreciated%3F%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-354292%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdvanced%20Filters%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-952672%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20advanced%20filter%20issues%20with%20multiple%20decimal%20style%20numbering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-952672%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288059%22%20target%3D%22_blank%22%3E%40tris_de%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20advanced%20filter%20to%20group%20by%20level%2C%20just%20write%20the%20following%20formula%20in%20the%20Criteria%20Range%3A%3CBR%20%2F%3E%3CSTRONG%3E%3D%20TEXT%20(%221%20*%22%3B%20%22General%22)%3C%2FSTRONG%3E%3CBR%20%2F%3EThe%20%3CEM%3E1%20*%3C%2FEM%3E%20represents%20everything%20that%20follows%20next%20to%20what%20is%20to%20the%20right%20of%20the%20%221%22.%3C%2FP%3E%3CP%3EThe%20%3CEM%3E%22General%22%3C%2FEM%3E%20is%20not%20essential%20but%20makes%20you%20sure%20the%20comparison%20is%20against%20a%20text.%3CBR%20%2F%3EHope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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!