How to split one column into two?

[Guest]

I have an EXCEL 97 worksheet that includes a column with birthdays in the
format 11/13/51, for instance, for November 13, 1951. I would like to split
that column into two columns. The first column would contain the month and
day (that is, 11/13) and the second column would contain the year (that is,
51). How can I do this?

 

[Max]

One way ..

Assuming the dates are in col A, A2 down,

Put:
In B2: =TEXT(DATE(YEAR(A2),MONTH(A2),DAY(A2)),"mm/dd")
In C2: =TEXT(DATE(YEAR(A2),MONTH(A2),DAY(A2)),"yy")

Select B2:C2, copy down

Col B will return the month/day,
col C will return the year

 

[Frank Kabel]

Hi
use if you want text values
=TEXT(A1,"MM/DD")
and
=TEXT(A1,"YY")

if you still want the date values but just displayed this way use
=A1
in both cells and apply a custom format ('Format -Cells') such as
MM/DD
and
YY

 

[Frank Kabel]

Hi Max
no need for first getting the Year, month, day
=TEXT(A2,"mm/dd")

for example would be sufficient )

 

[Max]

Thanks, Frank ! You're right.
Must have done something wrong first,
that's why it went the long route <g>

 

[Guest]

Frank:
I have applied the formula you suggested below on a different worksheet.
However, this time, it is just giving me back the same data. That is, even
though I'm using "=TEXT(A2,"mm/dd"), it is giving me back the month, day, AND
year, not just the month and day. I'm wondering if it has to do with the
format of my dates, which is, for example, for December 24, 2004:
12 24 2004

Do I need to change the format of my dates for the formula to now work? I
will appreciate advice.

 

[Ragdyer]

You're correct in that your date format is not recognized as a date by XL.

In order for Frank's formula to work you must first convert your data to a
"legal" format.
You can easily do this conversion using TTC, Text To Columns.

Select the column, then:
<Data> <TextToColumns> <Next> <Next>,
Under "Column Data Format", click on "Date",
And make sure that "MDY" is displayed in the date box.
Then <Finish>

Now you can apply Frank's formula.