+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Filter segmented data in a non standard format for formulas

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    25

    Smile Filter segmented data in a non standard format for formulas

    Im working on a project for the boss and need help with data like the following:

    001.002.001.003
    001.004
    001.004.001
    001.004.002
    001.004.005
    001.004.007
    001.004.007.002
    001.004.007.003
    001.004.009
    001.005
    001.005.001
    001.005.002
    001.007
    001.007.001
    001.007.002
    001.008
    001.008.002
    001.008.003
    001.008.005
    001.008.005.001
    001.008.006
    001.008.006.002


    It obviously accending but I cant get it to work with formulas or convert into a format that it can work with. I need to be able to asign a value based on one of these numbers...assuming these numbers are in column "A" in want to do something like the following =IF(A1>001.008,YES, NO)

    There are about about 1200-1500 series of numbers per sheet and I have several hundred sheets so deleting periods or changing the numbers would not be feasable...

    ANY HELP APPRECIATED!!!

    THANKS!
    Last edited by strokedmaro; 06-12-2009 at 06:04 AM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: trying to work with this data...odd numbering

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    re: Filter segmented data in a non standard format for formulas

    Thank you.

    =if(a1 > "001.008", "yes", "no")
    Last edited by shg; 06-11-2009 at 03:42 PM.

  4. #4
    Registered User
    Join Date
    06-09-2009
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Filter segmented data in a non standard format for formulas

    Quote Originally Posted by shg View Post
    Thank you.

    =if(a1 > "001.008", "yes", "no")
    Although I was able to use this method for my current purpose it would work incorrectly if the number was a series of 3.

    001.007.002 would be larger than 001.008 when use with this formula for example.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Filter segmented data in a non standard format for formulas

    Don't think so. Try this formula:

    ="001.007.002" > "001.008"

    Just make sure that all the entries are formatted as text. This returns a different result:

    ="001.007.002" > "001.008" + 0

    ... because "001.008" can be coerced to a number.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1