CODEWITHSUNDEEP
stringexceltexttimeformatting
Gareth Simpson
Gareth Simpson

Reputation: 37701

Convert time fields to strings in Excel

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 ?

Upvotes: 41

Views: 231708

Answers (6)

LukStorms
LukStorms

Reputation: 29677

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

Upvotes: 14

Makah
Makah

Reputation: 4523

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 & " Nice!"

End Function

Upvotes: 0

Robert Gamble
Robert Gamble

Reputation: 109182

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".

Upvotes: 93

Prakash Naykodi
Prakash Naykodi

Reputation: 1

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".

Upvotes: 2

Mark Chin
Mark Chin

Reputation: 51

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.

Upvotes: 5

Aditya Mukherji
Aditya Mukherji

Reputation: 9266

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

Upvotes: 34

Related Questions