Help - Parsing String from one sheet to another after using "Web Query"

[TXShooter]

I'm using Excel's "Web Query" to populate a sheet for NFL Sports Standings, but only need a few specific columns, and this website uses "0-0-0" for their Win-Loss-Tie column. Excel is putting that string into a single column. I need to break that down into three separate columns on a different sheet so that I can then import that second sheet (formatted differently) into a MS PowerPoint Presentation (which I wished that I could find a means to do so automatically, particularly because of the scrolling 'Credits' feature being used).

I have been limited to using Excel 2003 so hopefully that won't be an issue. Also, as anyone here knows football, those stats won't stay as a single digit per column thing, so that makes it a little more complex.

I tried using the "Text to Column" feature, but it won't work across two sheets in the same workbook (at least not in 2003). I'm having trouble trying to sort out the formula using "LEFT/MID/RIGHT" and "FIND", so I'm hoping someone here can point me into the right direction. I can get the first column for the "W" using the formula below, but it's the middle one that is throwing me off, especially if the number of digits fluctuate.

'A2'=(the team on that row)
'B2'=LEFT(Sheet2!B1,FIND("-",Sheet2!B1)-1) / The Win Column
'C2'= ??? / The Loss Column
'D2'=RIGHT(Sheet2!B1,FIND("-",Sheet2!B1)-1) / The Tie Column
'E2'=(the PCT for that team)

Any help would be greatly appreciated. Any extra help to get this automated on a weekly basis into a PowerPoint Slide would be HUGELY appreciated.

[Richard Buttrey]

Hi and welcome to the forum,

Please upload a workbook with some sample rows of data, and alongside manually enter the results you expect. That will allow us to work out the simplest way of achieving the goal.

[TXShooter]

Please upload a workbook with some sample rows of data, and alongside manually enter the results you expect. That will allow us to work out the simplest way of achieving the goal.

2013 NFL Standings.xls

Hopefully I did that correctly.

Since the season hasn't actually started yet, the Web Query is filled with zeros. I've modified it in a couple of places on the first sheet to see what it would do in the second sheet. The third sheet is a template of sorts for how I'd like the data to be shown when it's all said and done, but otherwise is just a manually filled-in form for Preseason Week 2. (Btw, this is American Football... I noticed you're from the UK.)

Thanks,
TXShooter

[Richard Buttrey]

Hi,

One way,
The formula for C3 copied down would be

Please Login or Register  to view this content.

Formatting the stuff will probably need a macro. That's tomorrow's task!

[TXShooter]

The formula for C3 copied down would be...

Formatting the stuff will probably need a macro. That's tomorrow's task!

Mighty fine formula there, Richard. However, what does the SUBSTITUTE do in this case?

I ask because my TIE column formula didn't work as well as I had hoped, and I'm trying to get a grasp on your formula.

Thanks,
TXShooter

[TXShooter]

Richard,
Based upon history, I've never seen the 'TIE' column go above a single digit, so I just used a formula to extract that digit where I need it to be. All other data is being placed to where I need them to be, many thanks for your 'L' column formula.

Now, as I mentioned before, I use the "WEB QUERY" from the DATA | IMPORT EXTERNAL DATA menu to fetch the statistics I need. This works beautifully, except I only know how to run the 'Refresh Data' manually after launching Excel on my local computer. Is there any way I can get it to automatically run every Tuesday morning without launching Excel?

The premise:
I have a PowerPoint SlideShow that runs 24/7 at a bar (pub) on many television screens, which includes photos of events, notifications, menu specials, etc., etc. In all there are 360 slides in this show, many repeating as advertisements. Several times during this show I want to repeat the NFL Standings for the previous week, to which I've made an Excel Spreadsheet gather that information semi-automatically.

The problem:
I do this as a favor (favour?) to my friends that run the bar during my downtime from work. That is about to change as I will be returning to work for several weeks at a time traveling about the USA. I've established the ability to remotely manipulate this slide show as need-be, but that requires for me to rebuild a new show with the updated information, upload it via a slow internet connection, blah, blah, blah... it takes nearly two hours. I don't have time to keep doing that weekly. I have found a means to have the 'linked' spreadsheet update within the show (AFTER the first loop, but not before for some reason). I'm not even certain that the computer running the 'show' has MS Excel on it.

I want to be able to have this whole thing run automatically to include the Excel information, maybe perhaps via the MS Scheduler function. I just don't know how to do that.

Would you or anyone have any ideas?

Attached is the latest and greatest.

Thanks,
TXShooter

2013 NFL Standings.xls

[newdoverman]

1/ Import the data.

2/ Do a search and replace for W-L and replace with W-L-T

3/ Select columns C and D and INSERT so that you have two empty columns next to column B

4/ Select column B and on the Data Tab, Select Text To Columns, Delimited and select Other, and use - as the delimiter.

5/ Click Finish.

The formulae in the Unformatted Data worksheet should fill in the data.

[TXShooter]

The formulae in the Unformatted Data worksheet should fill in the data.

Richard had pretty much answered my original query about how to extract the middle column's data. However, this workbook that you've answered with... what happens when you hit the 'Refresh Data' button? Does it not overwrite the information you've manually manipulated? I was looking for an automated manipulation.

Thanks,
TXShooter

[newdoverman]

That would require VBA.