Hi folks,

I am importing the NFL standings into a worksheet -
http://www.nfl.com/standings . In the Home, Road, AFC, NFC & DIV columns, the
team records are showing up as dates. Example, the Colts record on the NFL
site is showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 .929 409 206 6-1 7-0 11-1 2-0 6-0 Lost 1

In my workbook, it's showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 0.929 409 206 6/1 7/1 11/1 2/1 6/1 Lost 1

I've tried a number of different custom formatting options with no success.
Any advice would be appreciated

Bob

Copy the info to Notepad and save as NFL.txt file.

Open Excel and FileOpenfile TypeAll files *.*

Browse to Double-click on the NFL.txt file

The Text Wizard will pop up.

With Delimited checked hit NextNext.

Using SHIFT + Click select the columns with 6-1 and 7-0 etc. and Column Data
FormatTextFinish.


Gord Dibben Excel MVP

On Fri, 23 Dec 2005 09:53:02 -0800, "Bob in Charlotte" <Bob in
wrote:

Hi folks,

I am importing the NFL standings into a worksheet -
http://www.nfl.com/standings . In the Home, Road, AFC, NFC & DIV columns, the
team records are showing up as dates. Example, the Colts record on the NFL
site is showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 .929 409 206 6-1 7-0 11-1 2-0 6-0 Lost 1

In my workbook, it's showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 0.929 409 206 6/1 7/1 11/1 2/1 6/1 Lost 1

I've tried a number of different custom formatting options with no success.
Any advice would be appreciated

Select the spreadsheet or the number of columns you need and format the
cells as "TEXT." Then when you paste the information into excel -
select "PASTE SPECIAL" and past as "TEXT."


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=495814

Which sticks each row into Column A forcing OP to parse it out using Text to
Columns.

This then creates its own set of problems.


Gord

On Fri, 23 Dec 2005 12:59:25 -0600, wjohnson
wrote:


Select the spreadsheet or the number of columns you need and format the
cells as "TEXT." Then when you paste the information into excel -
select "PASTE SPECIAL" and past as "TEXT."

Thanks for the replies Gord & wjohnson. Those suggestions of copy/paste
special or importing the data from a txt file worked. Only thing is that I
want to automate it to refresh the data when opening up the file on Monday &
Tuesday mornings, after the games take place, but one does refresh the data,
it reverts back again to the date format.

Is their any formatting function I can apply to those cells to convert the
date format to a number to where the data shows up as 7-2 (whatever the
record.)

By the way, my apologies for the duplicate post. I put one post in via
usenet, then posted another post in this web forum, and wasn't aware that it
replicates to usenet till I saw the second post.

Bob

"Gord Dibben" wrote:

Bob
<snipped

"Bob in Charlotte" wrote:

Hi folks,

I am importing the NFL standings into a worksheet -
http://www.nfl.com/standings . In the Home, Road, AFC, NFC & DIV columns, the
team records are showing up as dates. Example, the Colts record on the NFL
site is showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 .929 409 206 6-1 7-0 11-1 2-0 6-0 Lost 1

In my workbook, it's showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 0.929 409 206 6/1 7/1 11/1 2/1 6/1 Lost 1

I've tried a number of different custom formatting options with no success.
Any advice would be appreciated

Not to beat a dead horse, but I'm guessing there is no simple way to format
those cells with any formula, after an automatic refresh takes place?

Bob

Bob

Formulas cannot format cells.

I managed to get it done directly by-passing the *.txt file.

Insert a new worksheet.

DataImport External DataNew Web Query.

Type or paste in the URL http://www.nfl.com/standings

That site will open and give you yellow arrows to select what you want.

Click on the Options button and checkmark FormattingNone Import<PRE blocks
into columns and Disable Date Recognition.

Hit Import.

Save the workbook with the query and next week just hit DataImport External
DataData Refresh.


Gord


On Fri, 23 Dec 2005 15:29:01 -0800, "Bob in Charlotte"
wrote:



"Bob in Charlotte" wrote:

Hi folks,

I am importing the NFL standings into a worksheet -
http://www.nfl.com/standings . In the Home, Road, AFC, NFC & DIV columns, the
team records are showing up as dates. Example, the Colts record on the NFL
site is showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 .929 409 206 6-1 7-0 11-1 2-0 6-0 Lost 1

In my workbook, it's showing up as:
Team W L T PCT PF PA Home Road AFC NFC DIV Streak
*yz-Indianapolis 13 1 0 0.929 409 206 6/1 7/1 11/1 2/1 6/1 Lost 1

I've tried a number of different custom formatting options with no success.
Any advice would be appreciated

Not to beat a dead horse, but I'm guessing there is no simple way to format
those cells with any formula, after an automatic refresh takes place?

Bob

Gord, that did it. Didn't even have to open a new worksheet,. Just opened my
original formatted sheet, right clicked to get "edit query", went into
options and took out that date function and replicated.

Very nice. Again ... Thank you for your current and prior suggestions.

Happy Holidays.

Bob

"Gord Dibben" wrote:

Bob

Formulas cannot format cells.

I managed to get it done directly by-passing the *.txt file.

Insert a new worksheet.

DataImport External DataNew Web Query.

Type or paste in the URL http://www.nfl.com/standings

That site will open and give you yellow arrows to select what you want.

Click on the Options button and checkmark FormattingNone Import<PRE blocks
into columns and Disable Date Recognition.

Hit Import.

Save the workbook with the query and next week just hit DataImport External
DataData Refresh.


Gort

Thanks for the feedback Bob.

We both learned something from this.


Gord

On Sat, 24 Dec 2005 05:29:02 -0800, "Bob in Charlotte"
wrote:

Gord, that did it. Didn't even have to open a new worksheet,. Just opened my
original formatted sheet, right clicked to get "edit query", went into
options and took out that date function and replicated.

Very nice. Again ... Thank you for your current and prior suggestions.

Happy Holidays.

Bob

"Gord Dibben" wrote:

Bob

Formulas cannot format cells.

I managed to get it done directly by-passing the *.txt file.

Insert a new worksheet.

DataImport External DataNew Web Query.

Type or paste in the URL http://www.nfl.com/standings

That site will open and give you yellow arrows to select what you want.

Click on the Options button and checkmark FormattingNone Import<PRE blocks
into columns and Disable Date Recognition.

Hit Import.

Save the workbook with the query and next week just hit DataImport External
DataData Refresh.


Gort