如何将科学数字转换为一般数字 [英] how to convert the scientific numbers to general

问题描述

我有一个带有18位数字列的模板，当输入时，最后一个数字将转换为零.所以我修改了列为文本列.我需要将xls转换为csv，当转换时我正在验证列(如果数字长度小于18)，我会将零填充到该数字.将其转换为csv后，数字将被修改为科学数字.有没有可能不将数字转换为科学数字.它在文本列中保持不变.预先感谢.

解决方案

我打算将其作为注释添加到上面的注释中，但是该选项对我而言没有出现，因此我将其作为答案.

首先，Excel仅允许您使用15位有效数字，这就是为什么您最初在结尾处得到零的原因.因此，是的，您必须将列设置为文本格式以处理18位输入.不过，正如Tim Williams指出的那样，虽然格式决定了您保存的文件的内容(这就是为什么在Wordpad中打开文件时会看到正确格式的原因)，但是在Excel中重新打开文件时却无济于事.

问题在于Excel非常热衷于帮助"您，以至于大多数情况下，当您打开.csv文件时，它都不会显示文本导入向导.相反，它将直接打开文件，从而对内容格式进行最佳猜测，并且在您的情况下，尽管存在前导零，但仍会猜错.

解决此问题的一种方法是将文件另存为.csv，但要确保文件具有不同的扩展名.我使用.cma.保存文件时，将其保存为"MyFile.cma"之类的文件，但必须在文件名"对话框中将该名称括在双引号中，否则Excel会将其保存为MyFile.cma.csv

打开.cma(或类似的其他无法识别的扩展名)时，Excel将显示文本导入向导，该向导允许您(如Tim所建议的那样)指定打开文件时希望该列为文本.这样，它就不会转换为数字，更具体地说，不会转换为科学数字.

另一个选择是使用VBA中的OpenText方法重新打开文件:

  Workbooks.OpenText FileName:= _"C:\ Users \ YourName \ Documents \ Data \ YourFile.cma"，_原点:= xlMSDOS，StartRow:= 1，数据类型:= xlDelimited，TextQualifier:= xlDoubleQuote，_ConsecutiveDelimiter:= False，Tab:= True，分号:= False，逗号:= True，_空格:= False，其他:= False，FieldInfo:= Array(Array(1，2))，TrailingMinusNumbers:= True 

这取决于您想要或需要为此付出多少努力来实现自动化.

I have a template with 18 digit number column, when input that last numbers will be converting to zeros. so i modified the column to Text column. I need to conver the xls to csv, when converting i am validating the column if the number length is less than 18 i am padding the zeros to that number. After converting it to csv the numbers will be modifying as scientific numbers. is there any possibility to dont convert the numbers to scientific. it remains same in the text column. Thanks in advance.

解决方案

I was going to add this as a comment to follow on from the ones above but that option isn't appearing for me so I'll put it as an answer.

On the first point, Excel only allows you 15 significant digits which is why you were getting zeroes at the end originally. So yes, you do have to format the column as text to handle 18 digit inputs. As Tim Williams pointed out, though, while the formatting determines the content of the file that you SAVE (which is why you see the correct formatting when you open it in Wordpad) it doesn't help when you reopen it in Excel.

The problem is that Excel is so keen to "help" you that in most cases it won't display the text import wizard when you open a .csv file. It will instead open the file straight up, making its best guess about the content format and in your case, despite the presence of the leading zeroes, it will guess incorrectly.

One way around this is to save the file as a .csv but ensure that it has a different extension. I use .cma. When you save the file you save it as something like "MyFile.cma" but you DO have to enclose that name in double quotes in the File Name dialog, otherwise Excel will save it as MyFile.cma.csv

When you open a .cma (or some other unrecognised extension like that) Excel will display the text import wizard, which allows you (as Tim suggested) to specify that you want the column to be text when the file is opened. In that way it won't convert to a number, and more specifically a scientific number.

The other option is to reopen the file using the OpenText method in VBA:

    Workbooks.OpenText FileName:= _
    "C:\Users\YourName\Documents\Data\YourFile.cma", _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, 
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
    Space:=False, Other:=False, 
    FieldInfo:=Array(Array(1, 2)), TrailingMinusNumbers:=True

It depends on how much effort you want or need to put into automating this.

这篇关于如何将科学数字转换为一般数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！