Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Cut part of cell

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Belgrade, Yugoslavia
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Paul, Sydney Australia
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Belgrade, Yugoslavia
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Belgrade, Yugoslavia
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)))

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Belgrade, Yugoslavia
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a lot, works fine ))
    Sax

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •