Share via

How to Filter numbers text column in Excel

Anonymous
2017-10-03T09:07:38+00:00

Hi,

I have a numeric data that has text formatting, and I want to filter small to big with using grouping. 

As you can see Filtered column shows the data in red, blue and black order. How do I do that?

NOW FILTERED
0100000 010
0100001 01099
01000 0109900
0109900 01000
01099 0100000
010 0100001
0110000 011
01100 01100
011 0110000
0120000 012
01200 01204
0120100 0120400
01201 01203
0120200 0120300
01202 0120310
0120300 0120320
0120310 01202
0120320 0120200
01203 01201
0120400 0120100
01204 01200
012 0120000

Kind Regards

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2017-10-03T11:55:03+00:00

    Hi,

    We tried replicating your concern and came to a close result. Presumably, you would like to filter the data from smallest to biggest while retaining the "0" before it, correct?

    If yes, then you would insert an apostrophe before it to retain the 0. In this case, Excel recognizes the cell contents as words instead of numbers.

    You would then need to exclude the apostrophe for Excel to recognize the cell contents as numerals.

    Kindly confirm if this is correct. If not, we'll give the appropriate troubleshooting steps.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-10-06T08:24:07+00:00

    We're glad your concern is resolved now! If you have any other questions, don't hesitate to let us know.

    0 comments
  2. Anonymous
    2017-10-03T13:08:43+00:00

    Thank you Jairti. It worked. 

    Kind Regards

    '010
    '01000
    '0100000
    '0100001
    '01099
    '0109900
    '011
    '01100
    '0110000
    '012
    '01200
    '0120000
    '01201
    '0120100
    '01202
    '0120200
    '01203
    '0120300
    '0120310
    '0120320
    '01204
    '0120400
    0 comments
  3. Anonymous
    2017-10-03T12:20:27+00:00

    Hi Jairti,

    Yes it is correct to say that.

    I need a filtering formula that sorts from smallest to biggest with leveling that is shown in below right column.

    Leveling (After sorting data look like th. Smallest to Biggest with Leveling Sorting)
    010 010
    01000 01000
    0100000 0100000
    0100001 0100001
    01099 01099
    0109900 0109900
    011 011
    01100 01100
    0110000 0110000
    012 012
    01200 01200
    0120000 0120000
    0120001 0120001
    0120002 0120002
    01201 01201
    0120100 0120100
    0120101 0120101
    0120102 0120102
    0120103 0120103
    0120104 0120104
    0120105 0120105
    01202 01202
    0120200 0120200
    0120201 0120201
    0120202 0120202
    0 comments