Removing Dates
 

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


--
Brento
------------------------------------------------------------------------
Brento's Profile: http://www.excelforum.com/member.php...o&userid=25213
View this thread: http://www.excelforum.com/showthread...hreadid=570702

Removing Dates
 

"Brento" wrote in
message ...

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

Removing Dates
 

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" wrote in
message ...

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

Removing Dates
 

I tried DataText 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
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