Excel changing formats of numbers to scientific and deleting leading zeros

I will look at adding an apostrophe in front and see if this helps. My worry will be that this will mess up some Access importing and linked tables that I use with Excel, or it may mess up importing/exporting

Correct!  You responded this way to __my__ response, as well as Rohn007's.  But note that I was contradicting Ronh007's suggestion.  I do __not__ believe that you should put an apostrophe prefix in the CSV file per se.  It will __not__ have the same effect as it does when we type into a cell.  It becomes part of the text; it is not a "prefix character" propterty, as VBA refers to it.  It will definitely "mess up" any references that do not expect an apostrophe as part of the text.  It is a misdirection and a waste of time to pursue, IMHO.

Yes, that is a long time issue

.

You have to be careful about importing CSV's.

.

The simplest way is to actually put an appostrophe at the start of the field with leading zeros. that will force excel to treat the column as text.

.

Here are some articles about various techniques to do tht

.

add leading zero to fixed number length in Excel? ****https://www.extendoffice.com/documents/excel/3713-excel-add-leading-zero-to-fixed-length.html

For example, you have a list of numbers with different character length which look untidy. For viewing neatly and clearly, you can add leading zero to fixed numbers with same length as below screenshot shown. This article, introduces the tricks on adding leading zero or trailing zero to fix number length in Excel.

.  *  Add leading zero to fixed number length with formula

.  *  Add trailing zero after decimal to fixed number length with formula

.

Handling Leading Zeros in CSV Files

https://excel.tips.net/T002588_Handling_Leading_Zeros_in_CSV_Files.html

When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel reflects what is really in the file. If you import a file and find that Excel strips off leading zeros from  what it imports, there are a number of possible reasons. This tip explains how you can track down the problem and correct it.

.

Leading 0- add leading zero to fixed number length in Excel? ****https://www.extendoffice.com/documents/excel/3713-excel-add-leading-zero-to-fixed-length.html

For example, you have a list of numbers with different character length which look untidy. For viewing neatly and clearly, you can add leading zero to fixed numbers with same length as below screenshot shown. This article, introduces the tricks on adding leading zero or trailing zero to fix number length in Excel.

.  *  Add leading zero to fixed number length with formula

.  *  Add trailing zero after decimal to fixed number length with formula

.

Leading 0- Add or Pad Leading Zeros To Numbers or text In Excel? ****https://www.extendoffice.com/documents/excel/792-excel-add-leading-zeros-to-number-text.html

If you have a list of numbers that you need to add or pad some leading zeros before the numbers, when typing the zeros manually, they will be removed by default. This article is going to show you some tutorials about how to add leading zeros to numbers or text in ranges of cells in Excel.

.

.  *  Method 1: Add or pad the leading zeros to numbers to equal certain length with Text function

.  *  Method 2: Add or pad the leading zeros to numbers to equal certain length with Format Cells function

.  *  Method 3: Add or insert the same number of leading zeros to numbers with Concatenate function ****

.

Use Text Format to Preserve Leading Zeros in Excel

http://chandoo.org/wp/2012/02/15/use-text-format-to-preserve-leading-zeros/

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?

Hi William, You are welcome.

Thank you.

Thank you Jegan, I went to your link and gave my suggestion.

I also went to the options and unchecked that rule.

Thank you for your help!

Hi WIlliam

You can raise your suggestions in Excel User voice for the 1 and 2.

For 3, You can go to File - Option - Formulas - Error Checking Rules - Uncheck the rules you don't needed.

Some thing like here:

https://excel.uservoice.com/forums/304921-excel...

regards