blocks|key|1328908|text|你也可以给你的值加上一个‘前缀，尽管这会标记单元格，直到你把它变成一个文本字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1328909|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can also just prefix your value with a ', though this flags the cell until you make it a text field.

blocks|key|1328952|text|虽然所有这些都是将数字转换为文本以便保留前导零的精确解决方法，但这不是问题的答案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1328953|您的问题的答案是，如果您希望Excel中的数字包含前导零，则不能包含该数字-至少在导入期间是这样。|1328954|无论出于什么原因，Microsoft开发人员都将Excel构建为将03读入一个数字，并截断前导零，结果为3。这是无法避免的。|1328955|这很不幸，因为所有的变通方法都假设您知道哪些列的值可能带有前导零，这样您就可以在事后采取措施尝试修复数据。|1328956|顺便说一下，Google+Sheets也做了同样的事情。|1328957|所以简短的答案是，没有电子表格应用程序(至少我知道)，您可以在csv文件中读取可能具有哦，比方说00453作为数字，并将其导入为00453而不是453的文件。|1328958|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

While all of this is accurate workarounds for converting a number to text so that the leading zeros will be preserved, it is not an answer to the question.

The answer to your question is that if you want a number with leading zeros in Excel, you cannot have it - at least during the import.

The Microsoft developers, for whatever reason built Excel to read in 03 as a number and truncate the leading zero, resulting in 3. There is no way to prevent this.

It is unfortunate because all workarounds assume you know what columns have values that might have leading zeros so that you can take steps * after the fact * to try and fix your data.

By the way, Google Sheets does the same thing.

So the short answer is that there is no spreadsheet application (at least that I am aware of) where you can read in a csv file that might have oh, say 00453 as a number and have it imported as 00453 and not 453.

blocks|key|1329008|text|您可以使用以下格式保存它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1329009|在excel中，您可以|1329010|1)通过选择适当的单元格，右击，选择“格式化单元”，将格式设置为“文本”。然后在"Number“选项卡下，选择"text”。每当您输入一个数字时，它都会被读取为文本|1329011|或|1329012|2)在任何数字前面加上撇号(')，Excel将“按原样”接受它|1329013|如果使用的是VBA，则可以执行以下操作|1329014|1)选择需要格式化的单元格，并更改数字格式选项|1329015|Range("A1").NumberFormat+=+"@“|1329016|读取数据(从Excel中的单元格或从VBA读取)时，它将包含输入的任何前导零。|1329017|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|Y|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|10|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|11|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|S|$]]

You can save it using the following format.

<h2>In excel, you can</h2>

1) set the format to "Text" by selecting the proper cell, right clicking, selecting "Format Cell". Then under the "Number" tab, select "text". Anytime you enter a number, it will be read as text

OR

2) precede any number with an apostrophe (') and Excel will accept it "as is"

<h2>If you are using VBA, you can do the following</h2>

1) Select the cell you need to format and change the number format option

Range("A1").NumberFormat = "@"

When you read the data (from a cell in Excel or from VBA), it will contain any leading zeros that were entered.

blocks|key|1114003|text|我不知道这是不是完美的工作，但是，我第一次尝试它，它是有效的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114004|在左边的单元格中，你看不到前导的0，我想添加前导的0。修复方法是使用公式="0"&A3(Cell+number)|offset|length|style|CODE|1114005|(请记住设置您的，它应该可以完美地工作，请参见下面的示例。|1114006|​|1114007|📷|atomic|1114008|1114009|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/295dbb76e1141210e9f6fb1e466b52ce.png|imageAlt^0|0|G|1|P|1|10|K|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@$D|10|E|11|F|G]|$D|12|E|13|F|G]|$D|14|E|15|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|16|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|17|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|18|8|@]|9|@$D|19|E|1A|1|1B]]|A|$]]|$1|O|3|K|5|6|7|1C|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W|X|-4]]]]

I don't know if this is perfect work around but, I tried it the first time and it worked.

On the left cell you don't see leading <code>0</code> and I wanted to add leading <code>0</code>. The fix is to use the formula <code>="0"&amp;A3(Cell number)</code>

(remember to set your and it should work perfectly see example below.

<img src="https://i.stack.imgur.com/JZCUt.png" alt="excel screenshot">

blocks|key|1114088|text|单击单元格，然后在“主页”选项卡上的“编号”组中，单击“编号”旁边的对话框启动器按钮图像。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114089|​|1114090|📷|atomic|offset|length|1114091|1114092|在“类别”列表中，单击“自定义”，然后在“类型”框中键入数字格式，例如，对于社会安全号码代码，键入000-00-0000；对于五位数字的邮政编码，键入00000。|1114093|https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7|1114094|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/f5095c1ecb394e7949da6abcb3212e92.jpeg|imageAlt|1|LINK|MUTABLE|url^0|0|0|0|1|0|0|0|0|0|34|1|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@$G|13|H|14|1|15]]|A|$]]|$1|I|3|C|5|6|7|16|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|17|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|18|8|@]|9|@$G|19|H|1A|1|1B]]|A|$]]|$1|N|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U|V|-4]]|W|$5|X|R|Y|A|$Z|M]]]]

Click the cell and On the Home tab, in the Number group, click the dialog box launcher Button image next to Number.

<img src="https://i.stack.imgur.com/KvBzh.jpg" alt="enter image description here">

In the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.

<a href="https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7" rel="nofollow noreferrer">https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7</a>

blocks|key|1114140|text|如果要导入/打开具有前导零的.csv文件，请使用Open+Office或Libre+Office。当您打开该文件时，它将显示一个对话框，用于指定列分隔符等内容。您还可以选择其中一列，并将类型指定为"text“，这将保留前导零。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114141|如果随后将其另存为.xlsx文件，则可以使用Excel打开该文件，并将保留前导0。|1114142|似乎应该有一种方法可以在导入到Excel时指定它，但我一直没有找到它。|1114143|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If you are importing / opening a .csv file that has leading zeroes, use Open Office or Libre Office.
When you open the file, it will present a dialog to specify things like the column delimiter. You can also select one of the columns and specify the type as "text" which will preserve the leading zeroes.

If you then save it as a .xlsx file, it can be opened with Excel and will preserve the leading 0.

It seems like there should be a way to specify this on import to Excel but I haven't been able to find it.

blocks|key|1329309|text|在以零开始的数字前添加撇号将保留完整的数字，但不会将撇号放在后面。例如:如果您的数字是08753，除非您键入'08753，否则excel会将数字截断为8753。Excel会用08753填充单元格。很简单！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1329310|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Adding an apostrophe before the number that starts with a zero will keep the full number but not post the apostrophe. For example: if your number is 08753 excel will truncate the number to 8753 unless you type '08753. Excel will then fill the cell with 08753. Easy!

blocks|key|1114250|text|如果您只是双击csv文本文件来打开它，Office+2010将打开它并截断所有前导零，因为它默认情况下是对每一列进行常规格式设置。相反，您可以导入文件:单击"Data“选项卡-+"Get+External+Data”选项包括"From+Text“-打开一个向导，您可以在其中标记要导入的文件，预览它，并将任何包含前导零的列的格式定义为”text“。以这种方式导入时，将保留前导零。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114251|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you just double click on a csv text file to open it, Office 2010 will open it and truncate any leading zeroes because it is general formatting every column by default. Instead, you can IMPORT the file: Click on the "Data" tab - the "Get External Data" options include "From Text" - that opens a wizard where you can tag the file to import, preview it, and define the format for any column containing lead zeroes as "text." When you import this way, the leading zeroes are preserved.

blocks|key|1114301|text|问题是实际的存储值与显示值...所有这些表示“修复”都没有解决存储值中前导零的丢失问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114302|因此，如果在单元格中输入值或导入最终结果是相同的，则无论单元格的格式约束如何，都不能保留存储值中的前导零。|1114303|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The problem is actual stored values vs. displayed values... ALL of these presentation "fixes" do not address the loss of leading zeros in the stored value.

So if you are entering a value into a cell or importing the end result is the same, you cannot preserve the leading zeros in the stored value REGARDLESS of formatting constraints on the cell.

If I paste <code>04562</code>, it automatically truncates the leading zero and stores it as <code>4562</code>. 

I want it to to store it as <code>04562</code>.

How to prevent automatic truncation of leading zeros in Excel cell

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

如果我粘贴04562，它会自动截断前导零并将其存储为4562。我希望它将其存储为04562。

问如何防止自动截断Excel单元格中的前导零
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何防止自动截断Excel单元格中的前导零EN