Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I am in the process of entering a set of data into Excel. Some of the data is in the form of 4-55. Put that into a "general" formatted cell and it changes it into a date--a totally different value.

    OK--so I go to the cell formatting and change it to "Text". I now get the text to stay in the form of 4-55............BUT................

    Go to sort, and Excel still thinks things are dates, and I get the following type of result:
    4-55
    1-66
    3-291
    Obviously Excel still thinks that 4-55 and 1-66 are dates, and is simply letting me see what I entered.

    IS THERE ANY WAY TO STOP THE MADNESS???
    [I have been here for years; I had to get things restarted]

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Add a space to the front of the text on entry
    Jerry

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Jerry Bernhardt View Post
    Add a space to the front of the text on entry
    Hmm--I have heard worse ideas. Thanks.

    [But seriously someone should send a message to Microsoft--isn't this a bit draconian even on your part. I mean, no way to shut it off?]
    [I have been here for years; I had to get things restarted]

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by JohnD1 View Post
    Hmm--I have heard worse ideas. Thanks.

    [But seriously someone should send a message to Microsoft--isn't this a bit draconian even on your part. I mean, no way to shut it off?]
    Yep, that is a work around but if you want a way to format the cells try a custom format of @
    Jerry

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by JohnD1 View Post
    I am in the process of entering a set of data into Excel. Some of the data is in the form of 4-55. Put that into a "general" formatted cell and it changes it into a date--a totally different value.

    OK--so I go to the cell formatting and change it to "Text". I now get the text to stay in the form of 4-55............BUT................

    Go to sort, and Excel still thinks things are dates, and I get the following type of result:
    4-55
    1-66
    3-291
    Obviously Excel still thinks that 4-55 and 1-66 are dates, and is simply letting me see what I entered.

    IS THERE ANY WAY TO STOP THE MADNESS???
    An alternative is to Type a single ' at the start rather than a space.

    '4-55 is treated as text. The ' does not show in the cell, and is not used if you take the content of the cell

    So for example Len with Space4-55 would return 5 but the correct 4 with '4-55.

    The ' is ignored by Excel, it just tells is it to treat the entry as Text rather than interpret in normal way.
    I would use this method rather than the space.
    Andrew

  6. #6
    Star Lounger
    Join Date
    Feb 2008
    Posts
    99
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Jerry Bernhardt View Post
    Yep, that is a work around but if you want a way to format the cells try a custom format of @
    Thanks even more. Yes, that works even better (I tried it in a sandbox). But, oh my, was that a nusiance to dig out of the "help" files.
    [I have been here for years; I had to get things restarted]

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's more cumbersome, but if you use the Data-Sort dialog (rather than the A-Z buttons) then tell it to sort numbers stored as text separately, it will sort your original data as it should. Once you have done that once, you should be able to use the A-Z and Z-A buttons as usual.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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