Is there anyway to store 1.2.3 as a number and not text? I have a formula that automatically creates a Work Breakdown Structure so root level items all have single values like 1,2,3 and as such are stored as numbers. So when I go to sort the data, its trying to sort numbers and text and so things dont come out as expected
Hi,
how about using a helper column to keep your data in the format you want, but use the help data column to sort. So enter 123 in A1, and put this in B1
=LEFT(A1,1)&"."&MID(A1,2,1)&"."&RIGHT(A1,1)
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
well, lets say i have 4 WBS: 1, 1.1, 1.1.1, 2
If i just stripped all the .'s then they would be 1, 11, 111, 2 but this would sort like 1, 2, 11, 111 which isn't what I want
The key is to store them all as text (even "1" and "1.1"), and ensure that WBS elements have sufficient digits at each level. For example, if the second level can have over nine elements, you need two digits to preserve natural sort order: 1.01, 1.02, ..., 1.09, 1.10, 1.11 ...
If the WBS numbers already exist and can't be modified, then you can use this function to generate a sort key in another column:
Please Login or Register to view this content.
Last edited by shg; 03-19-2008 at 12:02 PM.
Thanks! I used that function to generate a sort column and that worked perfectly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks