I need to convert a date value into three columns

[jcrenstrom]

I work with a lot of sports related data:
Wins - Losses - Ties is reprented as 10-15-1

When I copy and paste this information into excel, it converts it to a date
value
I need to get this information back into a text format and break it into
three columns.
A=10
B=15
C=1

How can I do this automatically, without having to manually enter it.

 

[Mike H]

Hi,

Pre-format the cell(s) as text or before you paste the values or type an
apostrophe ' you won't see it in the cell.

To break it into 3 columns, select the data and then

Data|Text to columns - select delimited - next
Check 'Other' and put a - sign in the 'other' box - Finish

Mike

 

[jcrenstrom]

I am taking the info from the web, so I formatted the column which the data
is being pasted to and formatted the column to text, it worked, with a few
anamolies.

10-17 becomes 17-Oct

Thus when using LEFT - MID - RIGHT functions I get :
40, 10

I do not understand the (') function.. how could this be helpful?

 

[Roger Govier]

Hi

Try formatting the column as Text, prior to posting any data.

 

[jcrenstrom]

Thanks roger... I did.

Here is what is wierd..
I am copying and pasting from a website.
I preformat the column to text prior to pasting.

It keeps all the cells in a text format, except when the first number is
less than the second number: For example: the website value of 17-10 works,
but a cell value of 10-17 gets converted to 17-Oct.

The first thing I tried was to paste special, but it will not allow me to
use this function becuase of other merged cells on the web site, not on my
spreadsheet, thus I can only use the normal paste function.

I cannot figure out why it is converting these values... when it is leaving
the other values as they are?

 

[Roger Govier]

Hi

The reason why it changes some and not others.
I guess you are set for American date format mm dd yyyy
Since 17 would be invalid as a month number, then it ignores a date
conversion for 17-10.
10 is a valid month number, hence 10-17 becomes 17-Oct

A solution might be to paste the values into Notepad, not into Excel.
Save the file as Filename.txt
In Excel File>Open>All files>Filename.txt
This will invoke the Data>Text to Columns Wizard>Next>Other>"-">Finish
You will than have your data in 3 columns all in one go.

 

[David Biddulph]

Preceding the data with an apostrophe forces it to be treated as text,
rather than being interpreted as a number or a date.