Excel problem with hypenated numbers

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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.
 

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.
 

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)
 

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.
 

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.
 

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
 

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.
 

Attachments

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.



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

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 ¬
 

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!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top