Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Stop auto Date format in excel?
I am importing Hockey Stats into excel automatically from a website (http://www.hockey-reference.com/leagues/NHL_2011_s...
Most stats are fine since they are just names and umbers, however when it is a record, excel always turns them into dates (if it can) Example, 12-5-8 becomes 12/5/2008. If have pre-formatted cells to text, general, etc... but no joy, still dates.
I have copied directly from the site into pre-formatted text cells, but wella Dates still arrive. I have adjusted download/connection options as much as I can but to no joy. Manually typing 30 teams worth of broken down records is out of the question.
Does anybody know how to stop excel from automatically turning these W-L-OTL/SOL numbers into a date when automatically updating from a website?
Using Microsoft Office 2010.
Glenn,
I have tried that, and it pastes all the data into one cell (okay easy enough to fix). Now going from text to columns, even in pre-formatted text cells, it will format those records into dates still.
Chaminda
You are a Hair and PC saver, thank you, that did the trick!
3 Answers
- voyagerLv 61 decade agoFavorite Answer
- Data tab - From Web
- Enter http://www.hockey-reference.com/leagues/NHL_2011_s...
- Click "Options" in the "New Web Query" dialog box
- Select "Disable Date Recognition"
- Click OK & Import
- ScrawnyLv 71 decade ago
I went to the website for the hockey and noticed at the top of the table, there is the opportunity to convert the table to CSV. That is Comma Separated Values. Click on that and copy the result.
Paste into Excel and use the Text to Columns feature. The file is Delimited with commas so choose that. You will then be presented with a table with the columns separated by vertical lines. In the upper part of the box you will notice a listing of formats. Starting at the first column of the data, click the first column and click on TEXT. Click on the next and then on TEXT. Do this for all the columns and when you are finished, you will have a perfect table created in Excel with all the numbers as text and in the proper columns.
Source(s): I just did the above and it works perfectly. - Anonymous1 decade ago
Copy the page >
Right click in cell >
Paste Special >
Select "Text" instead of "HTML"
This will strip all the formatting and your values will be inputted as text and not dates!
Best of luck!
Source(s): Excel Professional
