Convert Date Range in One Cell into 2 Cells

[ZoomT]

I have an Excel Sheet with a *Date Range * contained in *One Cell*, i
the following format:

Column-A

6/23/04 - 6/30/04 (in Cell A1, formatted as Text)
10/15/04 - 10/22/04 (in Cell A2, formmated as Text)
. . . . . . . . . . . . . . . . . (many more rows in Co
A)

How can I separate the 2 dates contained in the one cell, so that th
dates can be in their own separte Cells ? In my example, would wan
Cell A1's *1st date * of 6/23/04 placed in a new cell, and Cell A1'
*2nd date * of 6/30/04 placed in another new Cell.

Need a *VBA approach * and/or a *Formula approach * (other data i
the Sheet will be processed by some VBA code, so need to approach thi
in an
automated fashion. . .cannot use text-to-columns or similar manua
approaches).

Thanks,
Sherr

 

[Ron Rosenfeld]

I have an Excel Sheet with a *Date Range * contained in *One Cell*, in
the following format:

Column-A

6/23/04 - 6/30/04 (in Cell A1, formatted as Text)
10/15/04 - 10/22/04 (in Cell A2, formmated as Text)
. . . . . . . . . . . . . . . . (many more rows in Col
A)

How can I separate the 2 dates contained in the one cell, so that the
dates can be in their own separte Cells ? In my example, would want
Cell A1's *1st date * of 6/23/04 placed in a new cell, and Cell A1's
*2nd date * of 6/30/04 placed in another new Cell.

Need a *VBA approach * and/or a *Formula approach * (other data in
the Sheet will be processed by some VBA code, so need to approach this
in an
automated fashion. . .cannot use text-to-columns or similar manual
approaches).

Use the VB equivalent of text-to-columns.

Actually, just record a macro doing the text-to-columns routine, with <space>
as the delimiter and select to not import the column with the hyphens.

Then clean up the Selection part of the macro.


--ron

 

[Guest]

you may use the function mid
for example if u have "11/08/90 - 30/10/92" in A1, u can seperate them this
way,
click the cell u want to put first date and give the formula: =mid(A1;1;8)
then click the cell for the second date and give the formula as : mid(A1;12;8)
it may help