+ Reply to Thread
Results 1 to 10 of 10

returning date instead of number (format doesn't work)

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Groningen
    MS-Off Ver
    Excel 2010
    Posts
    25

    returning date instead of number (format doesn't work)

    Hi,
    I'm going crazy over an excel sheet in which I have numbers like 9/10. They're actually not fractions or anything, I just need them to stay the way they are while replacing in some of them \ with /. For some reason when I use ctrl F to replace all, the changed ones turn to numbers like 411960 or to a date when I try it manually. Obviously, I messed around with the format but nothing seems to work. I've tried basically every format and everything I can think of for the last couple of hours (I've even looked at control panel with regional and language options) but I can't get the stupid things to stay the way they are!

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: returning date instead of number (format doesn't work)

    you need to format cells as text BEFORE entering any data
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: returning date instead of number (format doesn't work)

    Format cells as text then enter your numbers.

  4. #4
    Registered User
    Join Date
    08-15-2012
    Location
    Groningen
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: returning date instead of number (format doesn't work)

    Thanks for your suggestions. Perhaps I'm misunderstanding but what I've done is: open a new workbook, select all --> format as text --> paste in my data (which is huge, so no option of manually re-entering it). If I then check how it sees the the data it does say 'text'. When I then try to do ctrl F to replace the wrong '\' with '/', they pop up as dates! and they've also changed the format to dates automatically!

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: returning date instead of number (format doesn't work)

    Prepare your sheet by formatting as text.

    Copy your source data and paste into word.

    Ctrl h to replace \ with / then copy the edited data from word.

    In excel right click, pastespecial > text.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: returning date instead of number (format doesn't work)

    I think the best way would probably be with VBA but I'm a novice with that so here's a more convoluted way. Let's say your data is pasted in A1:A10000
    In B1 (or any open column)
    ="''&A1 (that's a double quote followed by a single quote followed by a double quote)
    Copied down
    Copy Column B and paste special Values back into A
    Find and replace \ with /
    Now, if you still see a single quote in front of your numbers (you may not)
    Find and replace ' with nothing
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Registered User
    Join Date
    08-15-2012
    Location
    Groningen
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: returning date instead of number (format doesn't work)

    Yes!!! Finally, you are awesome :D. You saved my computer from being thrown out of the window.

    ---------- Post added at 04:00 PM ---------- Previous post was at 03:59 PM ----------

    Talking to JasonB...Although I'm sure your method would've worked as well .

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: returning date instead of number (format doesn't work)

    I concur, Jason is awesome.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: returning date instead of number (format doesn't work)

    Quote Originally Posted by ChemistB View Post
    I concur, Jason is awesome.
    I wish lol.

    I spent several hours searching for this a couple of years ago, and the last hour trying to remember how to do it again.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: returning date instead of number (format doesn't work)

    Paste as text would be a nice Paste Special option.

+ 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