Trouble pasting data in Excel in a workable format

Hi,

Straight off the top on this (as I don't know if this may affect things); I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).

In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);

http://stats.betradar.com/s4/?client...,231_full,23_2

The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).

I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).

The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.

In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?

I am happy to switch browser if it comes to it, as I like using Excel to do things with sports data (just for fun) and wouldn't rule that out.

Thanks in advance for any suggestions,

Hi,

Thanks. The link doesn't seem to work when I have pasted it here properly it should be (hopefully I've done this right - I don't really do tags!):

HTML Code: 

I have also pasted a workbook containing two sheets, showing what I get now and how things came in previously (columns A-H - everything else was formulas to manipulate A-H etc.)

Example Layout.xlsx

Hopefully this kind of explains what I am trying to do.

Thanks in advance for any suggestions.

Originally Posted by 6StringJazzer

Here is an illustration of what can be done--let me know if this helps.

1. I added a sheet to show what happens when I copy and paste data from the same web page. Mine pastes each line as a single line. I can't account for the difference, although maybe the Mac handles that text differently. To paste it properly I had to format F:J as Text to prevent Excel from trying to interpret some of the scores as dates. It's helpful that way I pasted as Paste Special>Text.
2. I removed the blank lines at the top of your "Now" sheet to keep things cleaner.
3. I made a copy of your "What I was doing" sheet and replaced your data with formulas to refer to the Now sheet.
4. The scores now use hyphens rather than colons. If it is important to you to use colons (never seen that for sports scores) then the formulas need a tweak.

This is admittedly a workaround rather than the ultimate solution. The ultimate solution would involve finding out why your data pastes onto multiple rows and fix that.

Hi 6String,

Thanks for having a go.

Re: point 1 - this was totally what I was getting when I used to paste in data from this site (with my old Mac Powerbook, Excel 2004 and Firefox). I am actually considering switching to a different browser temporarily to see if it is a Safari thing . For what it's worth - I also had to format F:J as text as well to prevent the same issue that you had (when pasting in currently under 2011 and previously under 2004).
2. Totally understood - I usually leave a couple of blank lines in on some sheets just to give myself some space until I know what I am going to do (and then take them out)
3/4. My workaround I had did pretty much the same thing but the data in A-I went in cleanly and I just took it from there and copied the subsequent formulas down to take what I wanted of the data.

I totally agree it's a great work around and thank you for trying, but the solution is going to be finding out why the data is now coming in on multiple lines (which I'm not sure if it is just down to me now having Excel for Mac 2011).

Hi,

Just to let anyone know that may have had a similar problem, I've switched browser and for some bizarre reason it appears to be a Safari thing (and maybe not a Mac or Excel for Mac 2011 thing). I've gone to the latest version of Firefox and it copies the text across into rows absolutely fine.

Thanks for the suggestions though,

Glad to hear you've resolved this. If your question has been answered please mark your thread as "Solved" so that members will know that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

Originally Posted by 6StringJazzer

Glad to hear you've resolved this. If your question has been answered please mark your thread as "Solved" so that members will know that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

Done (I wasn't sure if you could/were supposed to solve your own threads ). Thanks for the suggestions above by the way.