Jump to content


Photo

Readings Scores as dates or Millennial code

Test to Columns

  • Please log in to reply
3 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 12 July 2017 - 02:54 PM

I have asked before about getting Excel to read a game score as a game score instead of a date or millennial code. I've tried formatting the column containing scores to 'number format' and general format but I can't get Excel to bring in the score as a score when I try to separate column 'L'   with 'Text to Columns'

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 12 July 2017 - 04:38 PM

Hi JPK,

 

I've taken a look at your workbook and I assume you're referring to column M rather than L and that you are importing your data from a CSV file (as you were previously).

 

The scores in cells M11 and M16 were detected as dates and converted into a different format when the data was first imported into the workbook. Changing the format after the fact will not cause them to revert back to their original form.

 

In the latest version of Excel 2016, Microsoft have hidden the tool used in the solution shown in your previous thread and have replaced it with the new Get &Transform Data tools. The new tools are arguably better than the old, so the best solution may be to import your data using them. To do this:

  1. Click Data > Get & Transform Data > Get Data > From File > From Text/CSV.
  2. Navigate to your CSV file and click Import.
  3. Click the Load button.

The new data import tool doesn't perform any conversions by default, so you should find that all of the scores are displayed completely intact and haven't been converted into dates.

 

The Get & Transform Data tools are covered in depth in Session 11 of our Excel 2016 Expert Skills course, which is available from our books page.

 

You also re-enable the old Get External Data features if you would prefer to use them. To do this:

  1. Click File > Options.
  2. Click the Data tab.
  3. Under Show legacy data import wizards, make sure that From Text is checked.
  4. Click OK.

You can now use the old CSV import tool by clicking Data > Get & Transform Data > Get Data > Legacy Wizards > From Text (Legacy)

 

This tool is very similar to the Text to Columns tool, which is covered extensively in the video lesson: Expert Skills Lesson 2-2 Split delimited data using Text to Columns.

 

I hope one of these solutions is what you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 19 July 2017 - 12:53 PM

I've tried to use the Get data tab to open this type of file but to no avail. I notice that it says this is from a 1997-2003 WS. That may be the problem. 

I've tried to send you the file but it won't attach to your sheet.

 

Rk Tm Year Date Time LTime   Opp Week G# Day Result OT Rush Pass Tot TO 1 ATL 2016 12/18/2016 4:05     SFO 15 14 Sun W 41-13   141 137 278 0 2 ATL 2016 9/26/2016 8:30 7:30 @ NOR 3 3 Mon W 45-32   102 -134 -32 2 3 ATL 2016 11/3/2016 8:25   @ TAM 9 9 Thu W 43-28   66 -1 65 1 4 ATL 2016 11/27/2016 1:00     ARI 12 11 Sun W 38-19   58 -30 28 0 5 ATL 2016 10/23/2016 4:05 4:05   SDG 7 7 Sun L 30-33 OT 52 -92 -40

1

 

The result column has the dates I've alluded to. this is the column that I tried the Text To Columns. It works fine except if the scores can be read as a date as in 10-7. 

This sheet won't maintain the columnar format but I highlighted the affected scores in red.

 

Respectfully,

 

JPK1067



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 19 July 2017 - 07:34 PM

Hi JPK,

 

If your data originates in a 1997-2003 workbook (a .xls file rather than a .xlsx file), it is likely that the scores had already been converted into dates in that workbook. If you have access to a copy of the data before this happened, you should return to that copy and re-import it into Excel using the solution above.

 

If all you have is a .xls file with some of the scores converted into dates, your only possible solution is to find a way to convert them from dates back into textual scores. This is somewhat difficult, but possible.

 

In this example, I'll take cell M11 from your original workbook, showing the date 17-Oct. This represents the score 17-10. Realizing this, you can see that the solution will require extracting the days and months as 2-digit numbers.

 

You could do this using the DAY and MONTH functions, but the TEXT function offers a convenient alternative. The following formula will extract the days and months and display them as text in a dd-mm format:

=TEXT(M11,"dd-mm")

 

dd-mm is a custom format code. You can see a complete explanation of date formatting codes in the video lesson: Essential Skills Lesson 4-1 Format dates.

For a walkthrough of the TEXT function, see the video tutorial: Expert Skills Lesson 3-19 Use the TEXT function to format numerical values as strings.

 

Surprisingly, this formula is also capable of correctly interpreting your other scores such as 33-18, even though there is of course no such thing as a month with 33 days. By filling down with this formula, you should be able to convert all of your scores back into text.

 

Before splitting your text using Text to Columns, you will first have to use Paste Values to remove the formulas and replace them with the actual values that were calculated.

 

You can see how to use Paste Values in the video lesson: Essential Skills Lesson 3-5 Use Paste Values and increase or decrease decimal places displayed.

 

I have attached a copy of your workbook with the scores fully converted and split, including the formulas:

 

Attached File  ConvertedDates.xlsx   14.42KB   11 downloads

ConvertedDates.png

 

I hope this is the solution that you needed.

 

If you are still having problems and need to attach your example, you should be able to do this by saving it in the standard Excel Workbook format. You can see how to do this in the video lesson: Essential Skills Lesson 1-7 Understand common file formats.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users