Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Houston, Texas, USA
    Posts
    303
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number or Text (Excel 2002)

    I have a file that was given to me but all the information is in Text format. I have columns in there are supposed to time but I can convert the Text to a time format hh:mm:ss. In Column D it should show Elapsed Time 000:00:09, also in column F it should show mm:ss. Can someone show me how to convert these columns to the rigth format?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number or Text (Excel 2002)

    If there are values that really need the 3-digit hour format, things become complicated. Otherwise: in K4, enter the formula

    =TIMEVALUE(MID(D4,2,999))

    Then, select K4 again if necessary, then double click the fill handle in the lower right corner of the cell.
    Format the selection as [hhh]:mm:ss (select Custom and enter this format manually, it is not in the predefined list.)
    Copy to the clipboard.
    Select D1.
    Select Edit | Paste Special...
    Click the 'Values and Number Formats' option, then click OK.

    In L4, enter the formula

    =TIMEVALUE("0:"&F4)

    Then, select L4 again if necessary, then double click the fill handle in the lower right corner of the cell.
    Format the selection as mm:ss (there is such a format in the predefined list, or you can select Custom and enter mm:ss)
    Copy to the clipboard.
    Select F1.
    Select Edit | Paste Special...
    Click the 'Values and Number Formats' option, then click OK.

    You can now delete the formulas in columns K and L.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number or Text (Excel 2002)

    You can convert the values in columns B, C, G, and H to numeric by selecting each of those columns (one at a time), then select "Text to columns" from the Data menu. In the resulting dialog box click on Finish.

    The Elapsed Time and CPU Time columns must be converted using the procedure Hans outlined in his message.
    Legare Coleman

Posting Permissions

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