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 ?

Answer 1

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

Answer 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,"h:mm:ss")" (without the quotes), 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".

Answer 3

Well I dont know about you, but I was looking for a simple solution to a problem, which was answered perfectly by answer 2 above. It does exactly what it says on the tin.

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

Answer 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". I hope this is useful