how to convert the scientific numbers to general

Question

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.

Answer 1

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.