Only 30 years to fix this

[3DDave]

https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac

Now waiting for "Create format to convert to number and retain leading zeros."

Known issues
The feature does not support disabling these conversions during macro execution.

Of course. Why fix the feature most likely to suffer from automatic silent garbagication? Make them open 10,000 .csv files manually.

 

[SkipVought]

Now waiting for "Create format to convert to number and retain leading zeros."

So it appears that you want a NUMBER with leading zeros, yes?

Can you explain the purpose of a NUMBER with leading zeros? You can have a STRING of numeric characters with leading zeros.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[3DDave]

Because subtracting one string from another doesn't work correctly. Because creating more places for string to number conversions is a way to create difficult to find problems.

One could just use binary for all presentations, as long as the "endian" problem is solved.

 

[SkipVought]

But why the leading zeros? Why is that necessary for a NUMBER?

Could this be a vestige of 80 or 132 column cards or COBOL number pics?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[3DDave]

Maybe they should not, but then 90% of Excel should be done in better applications.

 

[SkipVought]

Yes, I wholeheartedly agree.

Excel is so "easy" that it's easy to use and abuse.

So in this instance I can propose at least two different scenarios to analyze:
1) NUMBERS from a legacy application in a TEXT file to analyze and possibly return
2) numeric identifiers (part numbers, invoice numbers, employee numbers etc) that will never be used to calculate a root or raised to a power.

In the former case, I recommend IMPORTING (never OPENING) via Data > Get External Data... and there are several methods therein available, that enable the user to specify fixed width or delimited, and Data Types. It is important to understand the types of data being imported and to specify accordingly. You may have some fixed width numeric data, but to manipulate mathematically, leading zeros are meaningless.

However, if data must be returned, then the proper column width must be imposed along with leading zeros for numeric data.

IDENTIFIERS as opposed to NUMBERS ought to be TEXT from beginning to end.

So I have waged a mini-crusade on this issue on the sister site with but a finger in the dike.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}