Excel does not do as you wish because it follows the following steps after determining the discrete items to consider (so after it figures out which characters belong to which "entities").
- Could the discrete item be a decimal system ("base 10") number? Do all of its characters fit a decimal system numeric pattern? For example, if there is an "A" in the string, then no, it does not fit, but an "E" in the string may be just fine. "," would be just fine. Once it has decided this, it moves on. If it decides "No" then it outputs the string as text. If "Yes" it will attempt to decide what kind of number. A date, for example. Being 4 to 8 numeric characters with "/" in the right places will usually guarantee a date is assumed and Excel will attempt to bring in such items as dates. And so on. If it tries for all its programmed patterns and it still cannot make what it sees as a valid number, and therefore result to output, it will default back to considering the item as a text string after all and output it as such. All done.
So it sees your input as likely to be a number and treats it as such. When it presents itself with the resulting value and finds it is nothing that it can interpret as a number, it makes it the text you are getting. So it gets a value like 1E-400 and thinks "number" and tries to present it as such. But it cannot create such a number in the user side of the program. (It can internally as it uses 27-28 decimal places internally, but it cannot exceed the limits Ramhound shows for calculation outputs.) It then goes to the default of "You are text."
There is NO solution "in Excel proper." As you mention, you might pre-filter the material. But a related program, presented as an internal function, called Power Query, can do this for you. Power Query ("PQ") is NOT limited by almost any of the traditional Excel limitations and might be of even more interest to you than just solving this problem for you.
Firstly though, if you are able to take each file you wish to import and rename it to a single, standard name you will always use for this, then do so. Create a PQ query to read the so-named file. Tell it to Load and Transform. Your data, as shown, will come in as a single column. Tell it to Split Columns and use a SPACE as a delimiter. Further tell it to use the leftmost space (since the number of spaces varies in your sample). Once the columns are split, highlight both and tell it to Change Type to "Decimal Number."
At this point you are done. You only need do this once. Since you used a special filename (the one you will rename files to be each time) you never need edit it to give it the next file's name. (There are other ways to achieve that, but that's simple so it's what I'm presenting here.) You can now tell it to Close & Load.
Your data comes into Excel as a Table. You will notice that, even though you may have seen PQ show the offending data as actual numbers that exceed Excel's limits while you were setting this up, that such numbers that exceed Excel's limits actually do import into the Table as 0's which you indicated would be acceptable.
So, easy-peasy.
However, depending upon the out-of-bounds data's value to you, you could consider doing your work all inside PQ. You could then use the offending values. It can do some amazing things and even more mundane things. It is a front-end to SQL. So it can do anything non-esoteric that you could do with an SQL program to take your data.
But in any case, it can solve today's difficulty.
A traditional Excel solution would be, say, to insert a helper column, type in a simple formula:
=IF(ISNUMBER(A1),A1,"")
and populate it down the helper column, Copy the helper column, and Paste|Special|Values overtop the source column (column A in thi formula's case). Then delete the helper column.
It's not elegant, and involves the dreaded "helper column" so many hate, but it works, works easily, and works well.
A different approach yet would be to cleanse each column after importing the data using VBA instead. It can test each imported cell's contents and delete the content if it is text leaving only numeric entries behind.
All depends upon your time, interests, and experience. Or as in my case, PQ seems exceedingly handy so if I have any time at all, I try to see what it can give me for anything new. But whatever works for you!
