I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff

With your posted "dates" in A1:A4

Try this:

The day/month:
B1: =IF(LEN(A1)4,LEFT(A1,LEN(A1)-4),A1)

The year:
C1: =RIGHT(A1,4)

Copy those formulas down as far as you need.

Those formulas return these values in B1:C4
20Feb 1950
8Jul 1980
1990 1990
4Jan 1979


Does that help?...or do you need something else?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Geoff" wrote in message
...
I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff

Correction....

B1 formula should be:
=IF(LEN(A1)4,LEFT(A1,LEN(A1)-4),"")

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
With your posted "dates" in A1:A4

Try this:

The day/month:
B1: =IF(LEN(A1)4,LEFT(A1,LEN(A1)-4),A1)

The year:
C1: =RIGHT(A1,4)

Copy those formulas down as far as you need.

Those formulas return these values in B1:C4
20Feb 1950
8Jul 1980
1990 1990
4Jan 1979


Does that help?...or do you need something else?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Geoff" wrote in message
...
I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or
2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff

Ron has given you the answer to the question you asked; however, if you
wanted to split the day from the month also, you could do this (assuming
your "dates" are in Column A)...

(Day) B1: =IF(LEN(A1)7,LEFT(A1,LEN(A1)-7),"")

(Month) C1: =SUBSTITUTE(LEFT(A1,LEN(A1)-4),B1,"")

(Year) D1: =RIGHT(A1,4)

The above assumes that when a month is present, that month is always
abbreviated to 3 letters.

Rick


"Geoff" wrote in message
...
I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff

On Mon, 26 Nov 2007 22:17:47 -0000, "Geoff" wrote:

I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff


Data in A2:a5

Year
B2: =RIGHT(A2,4)

DayMonth
C2: =SUBSTITUTE(A2,B2,"")

If your format is more variable than what you've posted, you'll need to let us
know.
--ron

Nicely done, Ron


"Ron Rosenfeld" wrote in message
...
On Mon, 26 Nov 2007 22:17:47 -0000, "Geoff" wrote:

I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff


Data in A2:a5

Year
B2: =RIGHT(A2,4)

DayMonth
C2: =SUBSTITUTE(A2,B2,"")

If your format is more variable than what you've posted, you'll need to
let us
know.
--ron

Use help from the following link to split text into diff parts
http://office.microsoft.com/en-us/ex...549011033.aspx
Then use the date func to convert the text to date



"Geoff" wrote:

I have a column of about 1000 dates in Excel 2000, all of them entered in
text format..
All the dates are different and written as in the following example:

20Feb1950
8Jul1980
1990
4Jan1979
etc

How can I separate just the year & place it in one column and the day and
month in another? I am having difficuly because the day has either 1 or 2
digits and some cells have only the year.

Any help would be much appreciated.

Geoff

On Mon, 26 Nov 2007 21:52:12 -0500, "Ron Coderre"
wrote:

Nicely done, Ron

Thank you.
--ron

Thanks Guys

Exactly what I needed.

Geoff