1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel problem with hypenated numbers

Discussion in 'Business Applications' started by stattad, Feb 3, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    I work for a sports publication, and we deal with wins and losses, such as 3-2. When I generate this in my database and paste it to Excel, SOMETIMES it will give me a date like March 2 instead of the 3-2. It only occurs to some of the numbers and I have tried formatting to text before pasting and trying all the Paste Specials. Any ideas how I can keep them consistent? Thanks.
     
  2. ddockstader

    ddockstader

    Joined:
    Oct 21, 2004
    Messages:
    126
    I'm not sure what database you are using, so I don't know how this translation will work. However, if you want to put numbers like 3-2 into a cell in Excel, if you precede that with an apostrophe, it will treat the numbers as text, i.e. '3-2 shows up in the cell as 3-2. Now if you do that in your database, I'm not sure it will translate into Excel and the apostrophe may show up as well. But it should be simple to test.
     
  3. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    Not sure I want to make global changes in my DB program (Paradox). I make a lot of charts from it, and only a couple go to Excel.
     
  4. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    Just format the cells (row/column) as text in excel. It should retain whatever is inserted.
    If you insert into a cell that has not got a specific format you will get a date (inputting that format of data)
     
  5. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    Doesn't work. As I stated in my original post, I have already tried that but when I paste the numbers some of them revert to dates.
     
  6. coachdan32

    coachdan32

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Are you formatting the whole column as text? DaveBurnett's suggestion should work.
     
  7. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    I just tried it again. Didn't work. Here's what I'm doing: Generate statistics in Paradox. Send to Word Perfect to "clean it up". Save as text file. Go to Excel. CTRL-A, format cells for text. At that point I can either OPEN the text file (which I think abandons the text format) or open it in another spreadsheet and paste it to the first one with the formatting. Neither way works.
     
  8. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    What is the exct format of the text file. I have just tried opening a text file in Excel and it took me though the text converter and asked me what the format was. It opened it exactly as you want- no dates
    Data was

    1-2
    2-1
    3-3
    4-4
    0-2
    saved as a txt file
     
  9. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    I enlcosed part of the file that gives me trouble. Most of the numbers at the far right come out as dates. Maybe we're talking about different versions. I still use Excel 97. I didn't like some of the features they did away with for Excel 2000.
     

    Attached Files:

  10. squidboy

    squidboy

    Joined:
    Dec 29, 2004
    Messages:
    403
    Using the import wizard (Excel 2000) and specifying columns as text allowed a successful import. I'm not 100% if your version will do the same but I seem to recall that it will.

    [​IMG]

    Note that this is step 3, or the last part before it gets dumped into the spreadsheet.
     
  11. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    Just as an aside, it would be a lot easier if you could put a unique character between the fields and make sure there aren't any other occurences actually in the data.
    One I use is ¬
     
  12. stattad

    stattad Thread Starter

    Joined:
    Jan 10, 2005
    Messages:
    27
    Squidboy wins!
    I had chosen text before, but I had no idea you had to specify which columns. I chose the last column, held down shift and picked the column next to it and voila! it worked perfectly. Thanks so much for the help!
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/326385

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice