Cut part of cell

sax

Board Regular
Joined
Feb 19, 2002
Messages
156
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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 )
 
Upvote 0
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
 
Upvote 0
Thanks for fast reply.
The cells are in General format.
Where do I put this formula that you gave me?
Thanks
 
Upvote 0
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.
 
Upvote 0
Thanks Iain,
works fine with Time but I would like to show Date and delete time.
Thanks
 
Upvote 0
Sorry - thought you wanted to cut Time & show it seperately ( guess I should've read your question properly :wink: ). Ok try either of these :

=TRUNC(B1,0)

or

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

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top