Removing Dates

[Brento]

Please ignore

[David Biddulph]

"Brento" <[email protected]> wrote in
message news:[email protected]...
>
> HI All,
>
> Hopefully a quick one. I have a serious of info in a cell in this
> format
>
> 11/08/2006 12:00:00
> 10/08/2006 12:15:00
>
> I need to keep the time but remove all the dates from the cell. I am
> using the replace function however the dates can only be run 1 day at a
> time and I have over a years worth to remove,
>
> Is there a generic format ie **/**/**** That will when put in the
> replace bar will remove all dates no matter when they are?

If the data is entered as a date & time, as distinct from as text, then you
can just reformat the cells.

If it is text, you may be able to split it using Data/ text to columns
(using the space as a delimiter), or more sensibly you may be able to use
the same function to convert the whole cell from text to date & time
(choosing an appropriate date format at the last stage of the wizard), then
reformat accordingly.
--
David Biddulph

[Arjuna]

hi,
use following function if your data is in text format:

=+TIME(MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),1,2),MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),4,2),MID(TRIM(MID(B15,SEARCH("
",B15,1)+1,LEN(B15))),7,2))

where B15 is cell containing your datetime val.



"David Biddulph" wrote:

> "Brento" <[email protected]> wrote in
> message news:[email protected]...
> >
> > HI All,
> >
> > Hopefully a quick one. I have a serious of info in a cell in this
> > format
> >
> > 11/08/2006 12:00:00
> > 10/08/2006 12:15:00
> >
> > I need to keep the time but remove all the dates from the cell. I am
> > using the replace function however the dates can only be run 1 day at a
> > time and I have over a years worth to remove,
> >
> > Is there a generic format ie **/**/**** That will when put in the
> > replace bar will remove all dates no matter when they are?
>
> If the data is entered as a date & time, as distinct from as text, then you
> can just reformat the cells.
>
> If it is text, you may be able to split it using Data/ text to columns
> (using the space as a delimiter), or more sensibly you may be able to use
> the same function to convert the whole cell from text to date & time
> (choosing an appropriate date format at the last stage of the wizard), then
> reformat accordingly.
> --
> David Biddulph
>
>
>

[Gord Dibben]

I tried Data>Text to Columns on your example dates/times and was sucessful in
splitting the time off from the date.

Fixed width.

Clear the break line between 12:15 & PM and don't import the date column.


Gord Dibben MS Excel MVP

On Fri, 11 Aug 2006 07:23:58 -0400, Brento
<[email protected]> wrote:

>
>HI All,
>
>Hopefully a quick one. I have a serious of info in a cell in this
>format
>
>11/08/2006 12:00:00
>10/08/2006 12:15:00
>
>I need to keep the time but remove all the dates from the cell. I am
>using the replace function however the dates can only be run 1 day at a
>time and I have over a years worth to remove,
>
>Is there a generic format ie **/**/**** That will when put in the
>replace bar will remove all dates no matter when they are?
>
>Thanks in advance
>Brento