Splitting dates

[Geoff]

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 Coderre]

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)

 

[Ron Coderre]

Correction....

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

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

Regards,

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[Ron Coderre]

Nicely done, Ron

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

 

[Anu]

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

 

[Ron Rosenfeld]

Nicely done, Ron

Thank you.
--ron

 

[Geoff]

Thanks Guys

Exactly what I needed.

Geoff