Display, sort and filter numbers with multiple decimal in excel 2007

I'm using excel 2007.

I've a list of tasks (200-500) that I need to group in different category/section etc (multiple filters). Whole data is in excel table so I can apply Excel's build-in table filters to display exact data that I need.

However it is always difficult to apply multiple filter to display expected data, specially as I need to do it very frequently. To make things simple I'm planning to number each record like

a.b.c.d.e.f 

Where a, b, c, d, e, f are simple numbers. List looks like:
1
1.1
1.2
1.2.1
1.2.1.1
1.2.2
1.3
& so on.

Problem is, Excel take it as number with single decimal but as soon as I add second decimal, excel treat it as text, which is obvious in general behavior.

However, as special case, I need excel treat both as number or text. Number is preferable as I want to sort them, which might be difficult as a text.

To make the things little more complex, while filtering in table, I require if I can add some formula to filter results like 1.* should display all numbers starts with 1.

Is it possible with excel's default behavior, without VBA?

If no, is it possible with VBA? If yes, any clue is appreciated. I don't need whole program as I can write basic VBA program, just a clue how it can be done?

Answers


I sort mine by adding a helper column that adds a letter to the front and sort on that. E.g. 1 becomes f1, 1.1 becomes f1.1 etc. Then all are sorted as text.

You can use the formula ="f" & A1.

My sample:

Then the data sorted:

And the filter:


Need Your Help

Model.objects.filter().update()

python django

How can I change [book must etc...] with real Django code in this scenario:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.