Excel paste gives dates when numbers needed
Moderator: Doctor MJ
Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Excel paste gives dates when numbers needed
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.
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..."
Re: Excel paste gives dates when numbers needed
- Nivek
- Head Coach
- Posts: 7,406
- And1: 959
- Joined: Sep 29, 2010
- Contact:
Re: Excel paste gives dates when numbers needed
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.
- 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.
-- Malcolm Gladwell
Check out my blog about the Wizards, movies, writing, music, TV, sports, and whatever else comes to mind.
Re: Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Re: Excel paste gives dates when numbers needed
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..."
Re: Excel paste gives dates when numbers needed
-
- 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
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....
Re: Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Re: Excel paste gives dates when numbers needed
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..."
Re: Excel paste gives dates when numbers needed
-
- 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
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....
Re: Excel paste gives dates when numbers needed
-
- Senior
- Posts: 678
- And1: 198
- Joined: Aug 10, 2014
Re: Excel paste gives dates when numbers needed
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.
Re: Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Re: Excel paste gives dates when numbers needed
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..."
Re: Excel paste gives dates when numbers needed
-
- Senior
- Posts: 678
- And1: 198
- Joined: Aug 10, 2014
Re: Excel paste gives dates when numbers needed
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.
Re: Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Re: Excel paste gives dates when numbers needed
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..."
Re: Excel paste gives dates when numbers needed
-
- Senior
- Posts: 678
- And1: 198
- Joined: Aug 10, 2014
Re: Excel paste gives dates when numbers needed
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.
Re: Excel paste gives dates when numbers needed
- molepharmer
- Head Coach
- Posts: 6,508
- And1: 1,122
- Joined: Feb 27, 2002
Re: Excel paste gives dates when numbers needed
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..."
Re: Excel paste gives dates when numbers needed
-
- 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
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....
Re: Excel paste gives dates when numbers needed
-
- Senior
- Posts: 678
- And1: 198
- Joined: Aug 10, 2014
Re: Excel paste gives dates when numbers needed
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