How to split one column into two?
 

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?

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
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Robert Judge" wrote in message
...
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?

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


--
Regards
Frank Kabel
Frankfurt, Germany


Robert Judge wrote:
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?

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

for example would be sufficient :-))


--
Regards
Frank Kabel
Frankfurt, Germany


Max wrote:
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
--
Rgds
Max
xl 97
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?

Thanks, Frank ! You're right.
Must have done something wrong first,
that's why it went the long route <g
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---
"Frank Kabel" wrote
Hi Max
no need for first getting the Year, month, day
=TEXT(A2,"mm/dd")

for example would be sufficient :-))


--
Regards
Frank Kabel
Frankfurt, Germany

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.

"Frank Kabel" wrote:

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

for example would be sufficient :-))


--
Regards
Frank Kabel
Frankfurt, Germany


Max wrote:
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
--
Rgds
Max
xl 97
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?

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Robert Judge" wrote in message
...
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.

"Frank Kabel" wrote:

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

for example would be sufficient :-))


--
Regards
Frank Kabel
Frankfurt, Germany


Max wrote:
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
--
Rgds
Max
xl 97
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?