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 ?

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Lunatik]

Had to upvote this one as I can't believe the other answer was accepted, makes SO look like a joke!

[Gareth Simpson]

Yes, because it makes so much sense to spend the extra time typing in a formula when the answer still boils down to "paste as text"

[overgroove]

It might be cheating but it totally solved a hair-pulling experience for me.

[sj59]

Edit your answer: =TEXT(A1,"hh:mm:ss") is true.

[user121391]

This is the quickest way if you need to work with values that are greater than 24 hours, because the TEXT method silently discards all remaining days (24 hour units). On the other hand, with copying you get the numerical value (approximately) and can then multiply with 24 to get your hours back!

[LukStorms]

The use of a , or ; in an excel function is actually a regional setting on your windows: the list separator. On my pc it was set to ;

[andreithegiant]

This worked for me. First answer above did not let me manipulate the cell containing the time, i.e. concatenating something to it would still return the excel timevalue. This happened even when the cell entry was preceded by a " ' ".

[philomath]

I got "h:02:00" instead in Excel 16.5 for Mac