Convert time fields to strings in Excel

Question

I have an excel sheet full of times   They are formatted so that they look like  1 00 15  However if I change the format on the cells to text  they change to the underlying numeric representation of the time  0 041840278  How can I convert the cells to be text cells but still have the time in them

User · Answer

Copy to a Date variable then transform it into Text with format    Example   Function GetMyTimeField       Dim myTime As Date  myStrTime As String      myTime    A1      myStrTime   Format myTime   hh mm       Debug Print myStrTime  amp    Nice    End Function

User · Answer

The below worked for me   First copy the content say  1 00 15  in notepad Then select a new column where you need to copy the text from notepad  Then right click and select format cell option and in that select  numbers tab and in that tab select the option  Text   Now copy the content from notepad and paste in this Excel column  it will be text but in format  1 00 15

User · Answer

copy the column paste it into notepad copy it again paste special as Text

User · Answer

The below worked for me   First copy the content say  1 00 15  in notepad Then select a new column where you need to copy the text from notepad  Then right click and select format cell option and in that select  numbers tab and in that tab select the option  Text   Now copy the content from notepad and paste in this Excel column  it will be text but in format  1 00 15

User · Answer

This kind of this is always a pain in Excel  you have to convert the values using a function because once Excel converts the cells to Time they are stored internally as numbers  Here is the best way I know how to do it   I ll assume that your times are in column A starting at row 1   In cell B1 enter this formula   TEXT A1  hh mm ss AM PM     drag the formula down column B to the end of your data in column A   Select the values from column B  copy  go to column C and select  Paste Special   then select  Values    Select the cells you just copied into column C and format the cells as  Text

User · Answer

If you want to show those number values as a time then change the format of the cell to Time   And if you want to transform it to a text in another cell    TEXT A1  hh mm ss

User · Answer

This kind of this is always a pain in Excel  you have to convert the values using a function because once Excel converts the cells to Time they are stored internally as numbers  Here is the best way I know how to do it   I ll assume that your times are in column A starting at row 1   In cell B1 enter this formula   TEXT A1  hh mm ss AM PM     drag the formula down column B to the end of your data in column A   Select the values from column B  copy  go to column C and select  Paste Special   then select  Values    Select the cells you just copied into column C and format the cells as  Text

User · Answer

If you want to show those number values as a time then change the format of the cell to Time   And if you want to transform it to a text in another cell    TEXT A1  hh mm ss

User · Answer

copy the column paste it into notepad copy it again paste special as Text

User · Answer

Easy  To change a time value like  1 00 15 to text  you can use the  TEXT  function  Example  if your time value  1 00 15  is contained in cell  A1   you can convert it into a text by doing  Text A1   h mm ss    The result still looks the same  1 00 15  But notice that this time round  it has become a text value

User · Answer

copy the column paste it into notepad copy it again paste special as Text

User · Answer

copy the column paste it into notepad copy it again paste special as Text

User · Answer

Copy to a Date variable then transform it into Text with format    Example   Function GetMyTimeField       Dim myTime As Date  myStrTime As String      myTime    A1      myStrTime   Format myTime   hh mm       Debug Print myStrTime  amp    Nice    End Function

User · Answer

This kind of this is always a pain in Excel  you have to convert the values using a function because once Excel converts the cells to Time they are stored internally as numbers  Here is the best way I know how to do it   I ll assume that your times are in column A starting at row 1   In cell B1 enter this formula   TEXT A1  hh mm ss AM PM     drag the formula down column B to the end of your data in column A   Select the values from column B  copy  go to column C and select  Paste Special   then select  Values    Select the cells you just copied into column C and format the cells as  Text

User · Answer

Easy  To change a time value like  1 00 15 to text  you can use the  TEXT  function  Example  if your time value  1 00 15  is contained in cell  A1   you can convert it into a text by doing  Text A1   h mm ss    The result still looks the same  1 00 15  But notice that this time round  it has become a text value

User · Answer

This kind of this is always a pain in Excel  you have to convert the values using a function because once Excel converts the cells to Time they are stored internally as numbers  Here is the best way I know how to do it   I ll assume that your times are in column A starting at row 1   In cell B1 enter this formula   TEXT A1  hh mm ss AM PM     drag the formula down column B to the end of your data in column A   Select the values from column B  copy  go to column C and select  Paste Special   then select  Values    Select the cells you just copied into column C and format the cells as  Text

[string] Convert time fields to strings in Excel

Examples related to string

Examples related to excel

Examples related to text

Examples related to time

Examples related to formatting