+ Reply to Thread
Results 1 to 6 of 6

Seperating data in cells

  1. #1
    Registered User
    Join Date
    12-27-2008
    Location
    Christchurch, New Zealand
    Posts
    92

    Seperating data in cells

    I have cells which display date and time in the below format (this cannot be changed).

    Thursday, November 23, 2017 04:13:00 p.m.
    Friday, November 24, 2017 12:21:00 a.m.

    I need to separate the time so it is in the format

    04:13:00 p.m.
    12:21:00 a.m.

    I have tried using text to columns with fixed width but this cuts off the hours due to the difference in column widths. Have also tried using space as a delimiter but that results in the incorrect time. Is there any way I can do this?
    Last edited by MikeNZ; 11-24-2017 at 06:44 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Seperating data in cells

    =MOD(A1,1) and format to the time foramt that you wish...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Seperating data in cells

    If all of your data has the format from above, try using
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-27-2008
    Location
    Christchurch, New Zealand
    Posts
    92

    Re: Seperating data in cells

    Not sure how that works. Does the A1 reference the original data, if so I have tried that and it just gives me #value!.

    When I separate the original using space as a delimiter it has "04:13:00 a.m." in one cell and "p.m." in the other therefore giving the incorrect time.

  5. #5
    Registered User
    Join Date
    12-27-2008
    Location
    Christchurch, New Zealand
    Posts
    92

    Re: Seperating data in cells

    Quote Originally Posted by PaulM100 View Post
    If all of your data has the format from above, try using
    Please Login or Register  to view this content.
    I think you've nailed it!!

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Seperating data in cells

    I think that Glenn's formula is a lot better as is specifically made to extract the time. You can use this as well
    Please Login or Register  to view this content.
    For both formulas to work, your A1 data has to be set as date and time format.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Seperating Data from one cell into multiple cells
    By Crofty92 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-16-2016, 10:41 AM
  2. Seperating Multiple Data in a Cell into Single Data Individual Cells
    By sgmgrider in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-22-2015, 11:47 AM
  3. Seperating Data in a cell to multiple cells
    By Geko in forum Excel General
    Replies: 2
    Last Post: 12-18-2007, 06:10 PM
  4. Seperating cells and columns
    By DRLski in forum Excel General
    Replies: 3
    Last Post: 09-01-2005, 01:05 PM
  5. [SOLVED] Seperating Data within cells
    By Tennisgirl in forum Excel General
    Replies: 2
    Last Post: 07-12-2005, 12:05 PM
  6. [SOLVED] Seperating duplicate cells
    By Hayley1982 in forum Excel General
    Replies: 2
    Last Post: 06-22-2005, 07:05 AM
  7. [SOLVED] seperating a group of common cells
    By ShineboxNJ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2005, 08:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1