Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
Change the width of a single column in a column chart | Charts and Charting in Excel | |||
how to fit my column unmoved eventhough i scroll down? | New Users to Excel | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |