string 在 Excel 中将时间字段转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/220672/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert time fields to strings in Excel
提问by Gareth Simpson
I have an excel sheet full of times.
我有一张装满时间的excel表。
They are formatted so that they look like: 1:00:15
它们的格式如下： 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
但是，如果我将单元格上的格式更改为文本，它们将更改为时间的基本数字表示形式：0.041840278
How can I convert the cells to be text cells but still have the time in them ?
如何将单元格转换为文本单元格但仍然有时间？
采纳答案by Aditya Mukherji
copy the column paste it into notepad copy it again paste special as Text
将列复制粘贴到记事本中再次复制粘贴特殊为文本
回答by Robert Gamble
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:
这种在 Excel 中总是很痛苦，您必须使用函数转换值，因为一旦 Excel 将单元格转换为时间，它们就会在内部存储为数字。这是我知道的最好的方法：
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".
我假设您的时间在 A 列中，从第 1 行开始。在单元格 B1 中输入此公式：=TEXT(A1,"hh:mm:ss AM/PM")将公式向下拖到 B 列到 A 列中数据的末尾。从 B 列中选择值，复制，去到 C 列并选择“选择性粘贴”，然后选择“值”。选择刚刚复制到 C 列中的单元格并将单元格的格式设置为“文本”。
回答by LukStorms
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")
回答by Mark Chin
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.
简单。要将诸如 1:00:15 之类的时间值更改为文本，您可以使用“TEXT”函数。例如，如果您的时间值 (1:00:15) 包含在单元格“A1”中，您可以通过执行以下操作将其转换为文本：Text(A1, "h:mm:ss")。结果看起来仍然相同：1:00:15。但请注意，这一次，它变成了一个文本值。
回答by Prakash Naykodi
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".
- 首先在记事本中复制内容说“1:00:15”
- 然后选择一个新列，您需要从记事本复制文本。
- 然后右键单击并选择格式单元格选项，并在该选项卡中选择数字选项卡，然后在该选项卡中选择选项“文本”。
- 现在从记事本复制内容并粘贴到此 Excel 列中。它将是文本，但格式为“1:00:15”。
回答by Makah
Copy to a Date variable then transform it into Text with format(). Example:
复制到 Date 变量，然后使用 将其转换为 Text format()。例子：
Function GetMyTimeField()
Dim myTime As Date, myStrTime As String
myTime = [A1]
myStrTime = Format(myTime, "hh:mm")
Debug.Print myStrTime & " Nice!"
End Function
