Excel paste gives dates when numbers needed

Moderator: Doctor MJ

User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Excel paste gives dates when numbers needed 

Post#1 » by molepharmer » Fri Jan 2, 2015 3:29 pm

I tried searching the web but didn't come up with any answers. Now figuring that if anybody knows the answer, it'd be somebody who routinely deals with box scores, and Excel.

Does anybody have a way of copying box scores, with hyphenated data (e.g. FGM-FGA) into Excel and it not showing up as a date? For example copying FG shooting 6-10, returns the date 6-Oct (or 10/6/2014, etc) when pasted into Excel. Also Excel retains this data as a date. So no amount of formatting seems to give numbers 6 and 10. That is, even doing 'data'/'text to columns' and delimited or fixed width produces a date.

I've tried to format the cells to number but that ends up giving a number > 4000 (which I think is something like the number of days since 1900, or some such nonsense). Other tricks I've tried was first pasting into Notepad and then pasting from Notepad into Excel. This helps but still leaves a long string of numbers which should have spaces (e.g. TRB, Stl, A, TO, PF, pts). Unfortunately within this long string of numbers some should be double digits and some single digits, so doing 'data' 'text to columns' doesn't help. I've even tried copy/paste from the web page's 'page source' but that also has major drawbacks.

If this was only a handful of rows, it wouldn't be a big deal. But I expect to be dealing with hundreds of rows.

fwiw - I want to use the game data from popcornmachine.net because they're the only site I've found that breaks everything down by quarters and by stint.
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
User avatar
Nivek
Head Coach
Posts: 7,406
And1: 959
Joined: Sep 29, 2010
Contact:
         

Re: Excel paste gives dates when numbers needed 

Post#2 » by Nivek » Fri Jan 2, 2015 9:46 pm

This is a royal pain in the ass for all of us. Possible fixes:

- paste in delimited format and then use the - as one of the delimiters
- paste in a word processor first, convert dashes into spaces, and then cut and paste into Excel

Let me know if you find something. I haven't tried these myself, but they MIGHT work.
"A lot of what we call talent is the desire to practice."
-- Malcolm Gladwell

Check out my blog about the Wizards, movies, writing, music, TV, sports, and whatever else comes to mind.
User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Re: Excel paste gives dates when numbers needed 

Post#3 » by molepharmer » Mon Jan 5, 2015 2:41 pm

Nivek wrote:This is a royal pain in the ass for all of us. Possible fixes:

- paste in delimited format and then use the - as one of the delimiters
- paste in a word processor first, convert dashes into spaces, and then cut and paste into Excel

Let me know if you find something. I haven't tried these myself, but they MIGHT work.

Couldn't really do your first suggestion, or at least I had no option to paste in delimited format but your second suggestion did give me an idea...and seems to be the best method I've found.
Basically:
1) select the box score data and use copy/'paste special' as text in Word
2) within Word use the edit/find/replace option to find 'special' 'tab character' (^t) and replace with a comma
3) do the same edit/find/replace and change hyphens to commas; you've now sort of created a CSV text document
4) paste the CSV text from Word into Excel and use the data/text to columns/delimited by a comma feature in Excel
5) this should give you what you want, and no stinking dates
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
panthermark
RealGM
Posts: 20,941
And1: 3,509
Joined: Mar 15, 2010
Location: Undisclosed: MJ's shadow could be lurking....
         

Re: Excel paste gives dates when numbers needed 

Post#4 » by panthermark » Mon Jan 5, 2015 9:31 pm

molepharmer wrote:
Nivek wrote:This is a royal pain in the ass for all of us. Possible fixes:

- paste in delimited format and then use the - as one of the delimiters
- paste in a word processor first, convert dashes into spaces, and then cut and paste into Excel

Let me know if you find something. I haven't tried these myself, but they MIGHT work.

Couldn't really do your first suggestion, or at least I had no option to paste in delimited format but your second suggestion did give me an idea...and seems to be the best method I've found.
Basically:
1) select the box score data and use copy/'paste special' as text in Word
2) within Word use the edit/find/replace option to find 'special' 'tab character' (^t) and replace with a comma
3) do the same edit/find/replace and change hyphens to commas; you've now sort of created a CSV text document
4) paste the CSV text from Word into Excel and use the data/text to columns/delimited by a comma feature in Excel
5) this should give you what you want, and no stinking dates

I live my life in excel....well I used to into I got promoted few months ago.
There are a couple of ways to fix it.
Post the data, highlight the column, then go to the data tab (Excel 2010). Hit the "Text to Columns" button. Delimited should pop up in Step 1...hit next. You can hit "next" again for Step 2. For Step 3, mark the "text" radio button. Hit finish.
If nothing changes, that is fine. Right click on the column you want to update and select "Format cells". The "Number" tab should default. Under "catagory", select "number". That should take care of it.
Jealousy is a sickness.......get well soon....
User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Re: Excel paste gives dates when numbers needed 

Post#5 » by molepharmer » Mon Jan 5, 2015 11:53 pm

panthermark wrote:I live my life in excel....well I used to into I got promoted few months ago.
There are a couple of ways to fix it.
Post the data, highlight the column, then go to the data tab (Excel 2010). Hit the "Text to Columns" button. Delimited should pop up in Step 1...hit next. You can hit "next" again for Step 2. For Step 3, mark the "text" radio button. Hit finish.
If nothing changes, that is fine. Right click on the column you want to update and select "Format cells". The "Number" tab should default. Under "catagory", select "number". That should take care of it.


I appreciate the help but it didn't work for me. When I copy/'paste special' the box score into Excel as text (or unicode text) all the spaces are omitted between As, Stl, Blck, To, pf, etc., so that those individual value numbers combine into one long string of digits. Doing the delimited and 'text' radio button have no effect on that long string of digits. So they can't be parsed out for their individual values (i.e. As, Stl, Blk, etc.) and format cells doesn't help at that point.
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
panthermark
RealGM
Posts: 20,941
And1: 3,509
Joined: Mar 15, 2010
Location: Undisclosed: MJ's shadow could be lurking....
         

Re: Excel paste gives dates when numbers needed 

Post#6 » by panthermark » Tue Jan 6, 2015 2:27 am

molepharmer wrote:
panthermark wrote:I live my life in excel....well I used to into I got promoted few months ago.
There are a couple of ways to fix it.
Post the data, highlight the column, then go to the data tab (Excel 2010). Hit the "Text to Columns" button. Delimited should pop up in Step 1...hit next. You can hit "next" again for Step 2. For Step 3, mark the "text" radio button. Hit finish.
If nothing changes, that is fine. Right click on the column you want to update and select "Format cells". The "Number" tab should default. Under "catagory", select "number". That should take care of it.


I appreciate the help but it didn't work for me. When I copy/'paste special' the box score into Excel as text (or unicode text) all the spaces are omitted between As, Stl, Blck, To, pf, etc., so that those individual value numbers combine into one long string of digits. Doing the delimited and 'text' radio button have no effect on that long string of digits. So they can't be parsed out for their individual values (i.e. As, Stl, Blk, etc.) and format cells doesn't help at that point.

OK,
That is easy to fix as well.
I'm working from home/memory, but instead of simply hitting next for Step 2, you can put a checkmark in the "comma" box. Doing that will split out a column for each comma. Just make sure you have enough empty columns for each new stat column.
Jealousy is a sickness.......get well soon....
TigerInYourTank
Senior
Posts: 678
And1: 198
Joined: Aug 10, 2014

Re: Excel paste gives dates when numbers needed 

Post#7 » by TigerInYourTank » Wed Jan 7, 2015 7:16 am

I used to deal with very dense genetic data that had labels which Excel would convert to dates. Very frustrating. The thing I always fell back on was to programatically add an apostrophe before the label so that it would not get converted. Then, you can use some Excel formulas to parse that data back out and do math on them.
User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Re: Excel paste gives dates when numbers needed 

Post#8 » by molepharmer » Wed Jan 7, 2015 12:42 pm

TigerInYourTank wrote:I used to deal with very dense genetic data that had labels which Excel would convert to dates. Very frustrating. The thing I always fell back on was to programatically add an apostrophe before the label so that it would not get converted. Then, you can use some Excel formulas to parse that data back out and do math on them.

In a rudimentary way, that's kind of, sort of what I'm doing when I first paste the data into Word. I'm basically using Word to find/replace (think program) the spaces and hyphens in the box score data with commas (as opposed to apostrophes) and making CSV text, which is easily pasted into Excel and delimited (or parsed) into separate columns for each value.
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
TigerInYourTank
Senior
Posts: 678
And1: 198
Joined: Aug 10, 2014

Re: Excel paste gives dates when numbers needed 

Post#9 » by TigerInYourTank » Wed Jan 7, 2015 4:47 pm

molepharmer wrote:
TigerInYourTank wrote:I used to deal with very dense genetic data that had labels which Excel would convert to dates. Very frustrating. The thing I always fell back on was to programatically add an apostrophe before the label so that it would not get converted. Then, you can use some Excel formulas to parse that data back out and do math on them.

In a rudimentary way, that's kind of, sort of what I'm doing when I first paste the data into Word. I'm basically using Word to find/replace (think program) the spaces and hyphens in the box score data with commas (as opposed to apostrophes) and making CSV text, which is easily pasted into Excel and delimited (or parsed) into separate columns for each value.


If that works for you, great, it's just that I think of commas as fundamental delimiters that have to be dealt with carefully. Sometimes doing a global replace leaves you with non-uniform lines...one line has 4 columns, say, and another has 5 due to an extraneous dash somewhere.

Personally, I would never use Word for this task. My workflow was to paste into notepad, do a global replace on <tab> or something to get comma-delimited (if it wasn't already comma-delimited), save the file as a csv file, then open in Excel to see what the damage was. If you see the idiotic date conversion, do a global replace using a regular expression. Something like this would deal with all "number-dash-number" patterns (like "7-8"):

[0-9]+-[0-9]

I think I do vaguely remember now being able to open an Excel sheet and setting the format of all the necessary columns before pasting in the data, in such a way that it did not convert anything at all. It is as if Excel programmatically added the apostrophe for me in every single cell to prevent the silly issues with automatic conversion. I wish I could remember more about this.
User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Re: Excel paste gives dates when numbers needed 

Post#10 » by molepharmer » Thu Jan 8, 2015 1:02 pm

TigerInYourTank wrote:If that works for you, great, it's just that I think of commas as fundamental delimiters that have to be dealt with carefully. Sometimes doing a global replace leaves you with non-uniform lines...one line has 4 columns, say, and another has 5 due to an extraneous dash somewhere.

I have a relatively easy fix for the extra column, non-uniform lines issue. In the past I've dealt with it a lot. So if an extra column would happen, it wouldn't bother me.

Personally, I would never use Word for this task. My workflow was to paste into notepad, do a global replace on <tab> or something to get comma-delimited (if it wasn't already comma-delimited), save the file as a csv file, then open in Excel to see what the damage was. If you see the idiotic date conversion, do a global replace using a regular expression. Something like this would deal with all "number-dash-number" patterns (like "7-8"):

[0-9]+-[0-9]


Initially I tried using Notepad (and Wordpad) rather than Word but those programs didn't seem to allow me to find a tab or space and replace with a comma (or apostrophe). Turns out, after a quick google search this morning, I found there is a trick to where you can get Notepad to find the tabs (or spaces) and replace with what ever you want.

Your point about using commas to replace tabs/spaces is well taken. It might be better to replace with an apostrophe (or something else), paste into Excel, then 'text to columns' 'delimited' 'apostrophes' (or something else).

I think I do vaguely remember now being able to open an Excel sheet and setting the format of all the necessary columns before pasting in the data, in such a way that it did not convert anything at all. It is as if Excel programmatically added the apostrophe for me in every single cell to prevent the silly issues with automatic conversion. I wish I could remember more about this.

I may look into this. Never tried it.
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
TigerInYourTank
Senior
Posts: 678
And1: 198
Joined: Aug 10, 2014

Re: Excel paste gives dates when numbers needed 

Post#11 » by TigerInYourTank » Thu Jan 8, 2015 4:56 pm

molepharmer wrote:
TigerInYourTank wrote:If that works for you, great, it's just that I think of commas as fundamental delimiters that have to be dealt with carefully. Sometimes doing a global replace leaves you with non-uniform lines...one line has 4 columns, say, and another has 5 due to an extraneous dash somewhere.

I have a relatively easy fix for the extra column, non-uniform lines issue. In the past I've dealt with it a lot. So if an extra column would happen, it wouldn't bother me.

Personally, I would never use Word for this task. My workflow was to paste into notepad, do a global replace on <tab> or something to get comma-delimited (if it wasn't already comma-delimited), save the file as a csv file, then open in Excel to see what the damage was. If you see the idiotic date conversion, do a global replace using a regular expression. Something like this would deal with all "number-dash-number" patterns (like "7-8"):

[0-9]+-[0-9]


Initially I tried using Notepad (and Wordpad) rather than Word but those programs didn't seem to allow me to find a tab or space and replace with a comma (or apostrophe). Turns out, after a quick google search this morning, I found there is a trick to where you can get Notepad to find the tabs (or spaces) and replace with what ever you want.

Your point about using commas to replace tabs/spaces is well taken. It might be better to replace with an apostrophe (or something else), paste into Excel, then 'text to columns' 'delimited' 'apostrophes' (or something else).

I think I do vaguely remember now being able to open an Excel sheet and setting the format of all the necessary columns before pasting in the data, in such a way that it did not convert anything at all. It is as if Excel programmatically added the apostrophe for me in every single cell to prevent the silly issues with automatic conversion. I wish I could remember more about this.

I may look into this. Never tried it.


I don't know why I didn't think to recommend this earlier: download notepad++, as it is free and is much more useful than just notepad. For example, if you had still been stuck on the issue of finding tabs, you can show all characters, which displays the tabs and line endings, or something like that. I use notepad++ all the time rather than notepad and I can't recommend it highly enough.
User avatar
molepharmer
Head Coach
Posts: 6,508
And1: 1,122
Joined: Feb 27, 2002

Re: Excel paste gives dates when numbers needed 

Post#12 » by molepharmer » Sun Jan 11, 2015 2:30 pm

TigerInYourTank wrote:I don't know why I didn't think to recommend this earlier: download notepad++, as it is free and is much more useful than just notepad. For example, if you had still been stuck on the issue of finding tabs, you can show all characters, which displays the tabs and line endings, or something like that. I use notepad++ all the time rather than notepad and I can't recommend it highly enough.


Good grief. If nothing else comes from this thread, upgrading to Notepad++ has been rewarding enough. No comparison to lowly Notepad. Thanks.
TGibson (1/28/17); "..."a 4 or 5 on a scale of 1 to 10 for drama"...What's the worst? "...yelling matches with Thibs, everybody is just going crazy and I'm just sitting there...like, 'Don't call my name please..."
panthermark
RealGM
Posts: 20,941
And1: 3,509
Joined: Mar 15, 2010
Location: Undisclosed: MJ's shadow could be lurking....
         

Re: Excel paste gives dates when numbers needed 

Post#13 » by panthermark » Mon Jan 12, 2015 4:48 pm

Where is the data source? I'm pretty sure I can turn it into a clean excel file. I spent over a decade turning damn near everything into in excel file so we could lot the data into our underwriting systems.
Jealousy is a sickness.......get well soon....
TigerInYourTank
Senior
Posts: 678
And1: 198
Joined: Aug 10, 2014

Re: Excel paste gives dates when numbers needed 

Post#14 » by TigerInYourTank » Thu Jan 15, 2015 6:51 pm

I want to use the game data from popcornmachine.net because they're the only site I've found that breaks everything down by quarters and by stint.

Return to Statistical Analysis