Cut part of cell

[sax]

Hi all,
I have large amount of data in one column wich contains date and time in each cell (01/04/2002 11:12:49)

I would like to cut time.
Any formula solution ?
Thanks in advance.
Sax

[Iain Lewis]

Hi,

There are 2 ways of doing this. The first assumes that B2 contains the date & time as text

=TRIM(MID(B1,SEARCH(" ",B1),80))

The second assumes that B1 is in the Date / Time format

=B1-TRUNC(B1,0)

For option 2 you will need to set the cell format to Time. Be aware that you will still have a date value before the time ( ie the cell will read 00000.467 which XL thinks is 00-Jan-00 11:12 )

[EBK]

What do you mean "I would like to cut time",

do you want to copy the time information to another cell or do you want to remove the time section completely.

Paul

[sax]

Thanks for fast reply.
The cells are in General format.
Where do I put this formula that you gave me?
Thanks

[Iain Lewis]

Where ever you want to show the Time. eg if you have the data in B2 to B10 & you want to see the Time in C2 to C10 put the formula in C2 and copy it down to C10.

Or put the formula in C2, highlight the range you want to "Fill" with the formula ( eg C2 to C10 ) & press Control + D. This will "FillDown" the range with what ever is in the first cell of the range.

[sax]

Thanks Iain,
works fine with Time but I would like to show Date and delete time.
Thanks

[Iain Lewis]

Sorry - thought you wanted to cut Time & show it seperately ( guess I should've read your question properly ). Ok try either of these :

=TRUNC(B1,0)

or

=TRIM(LEFT(B1,SEARCH(" ",B1)))

[sax]

Thanks a lot, works fine ))
Sax