数字更改为Excel中的日期

最新推荐文章于 2024-09-01 16:57:39 发布

culiao2169

最新推荐文章于 2024-09-01 16:57:39 发布

阅读量231

点赞数

文章标签： python java excel 大数据数据库

原文链接：https://contexturesblog.com/archives/2016/06/09/numbers-change-to-dates-in-excel/

版权

Have you ever copied football scores, such as 3-2, and pasted them into Excel, where they magically change to dates? It's certainly annoying when a nice list of numbers change to dates in Excel, but fortunately there's an easy way to prevent that from happening.

您是否曾经复制足球比分(例如3-2)并将其粘贴到Excel中，从而神奇地更改了日期？当一个好的数字列表在Excel中更改为日期时，这确实很烦人，但是幸运的是，有一种简单的方法可以防止这种情况的发生。

数字样本清单 (Sample List of Numbers)

If you're not quite sure what I'm talking about, copy the three numbers below, and paste them onto an Excel worksheet.

如果您不太确定我在说什么，请复制下面的三个数字，然后将其粘贴到Excel工作表中。

3/4
3/4
02345
02345
1-3
1-3

The results will vary slightly, depending on your Regional settings – Excel might see the numbers as a month/day or day/month

结果会略有不同，具体取决于您的区域设置-Excel可能会将数字视为月/日或日/月

In the screen shot below, you an see what happened when I pasted the numbers into Excel. Two of the numbers were changed to dates, and the leading zero was dropped from the other number.

在下面的屏幕快照中，您可以看到将数字粘贴到Excel中时发生的情况。其中两个数字更改为日期，而前一个零从另一个数字中删除。

3/4 became March 4th, the leading zero was dropped from the second number, and 1-3 turned into January 3rd.

3/4变成3月4日，前导零从第二个数字掉落，而1-3变成1月3日。

Thanks, Excel, but that's not quite what I wanted!

谢谢，Excel，但这不是我想要的！

防止数字格式丢失 (Prevent Number Formatting Loss)

If you want to copy those types of numbers, and keep their original formatting, you just have to do a little extra work – nothing complicated.

如果要复制这些类型的数字并保持其原始格式，则只需要做一些额外的工作-没什么复杂的。

First, get the worksheet ready:

首先，准备工作表：

In Excel, select the columns where you're going to paste the data. If you're copying two columns from a website, select the same number of columns on the worksheet.
在Excel中，选择要粘贴数据的列。如果要从网站复制两列，请在工作表上选择相同数量的列。
Then, on the Ribbon's Home tab, in the Number group, click the drop down arrow, and click on Text – it's way down at the bottom of the list
然后，在功能区的“主页”选项卡上的“数字”组中，单击下拉箭头，然后单击“文本” –它在列表底部

仔细粘贴 (Paste Carefully)

After you've got the worksheet ready, follow these steps to paste the data and keep its formatting.

准备工作表后，请按照以下步骤粘贴数据并保持其格式。

Select the data and copy it
选择数据并复制
On the worksheet, go to the column(s) that you formatted, and right-click the starting cell where you want to paste the data
在工作表上，转到格式化的列，然后右键单击要粘贴数据的起始单元格
In the popup menu, under Paste Options, click Match Destination Formatting
在弹出菜单的“粘贴选项”下，单击“匹配目标格式”。