Importing data, and need formatting advice

[Bob in Charlotte]

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

[wjohnson]

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."

[Gord Dibben]

Bob

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

Open Excel and File>Open>file Type>All files *.*

Browse to Double-click on the NFL.txt file

The Text Wizard will pop up.

With Delimited checked hit Next>Next.

Using SHIFT + Click select the columns with 6-1 and 7-0 etc. and Column Data
Format>Text>Finish.


Gord Dibben Excel MVP

On Fri, 23 Dec 2005 09:53:02 -0800, "Bob in Charlotte" <Bob in
[email protected]> 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

[Gord Dibben]

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
<[email protected]> 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."

[Gord Dibben]

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
<[email protected]> 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."

[Bob in Charlotte]

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]

"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 Dibben]

Bob

Formulas cannot format cells.

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

Insert a new worksheet.

Data>Import External Data>New 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 Formatting>None Import<PRE> blocks
into columns and Disable Date Recognition.

Hit Import.

Save the workbook with the query and next week just hit Data>Import External
Data>Data Refresh.


Gord


On Fri, 23 Dec 2005 15:29:01 -0800, "Bob in Charlotte"
<[email protected]> 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

[Bob in Charlotte]

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.
>
> Data>Import External Data>New 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 Formatting>None Import<PRE> blocks
> into columns and Disable Date Recognition.
>
> Hit Import.
>
> Save the workbook with the query and next week just hit Data>Import External
> Data>Data Refresh.
>
>
> Gort

[Gord Dibben]

Thanks for the feedback Bob.

We both learned something from this.


Gord

On Sat, 24 Dec 2005 05:29:02 -0800, "Bob in Charlotte"
<[email protected]> 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.
>>
>> Data>Import External Data>New 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 Formatting>None Import<PRE> blocks
>> into columns and Disable Date Recognition.
>>
>> Hit Import.
>>
>> Save the workbook with the query and next week just hit Data>Import External
>> Data>Data Refresh.
>>
>>
>> Gort