Select only non-integers (doubles/floating points) in excel spreadsheet

Question

I hope you understand the difference between an integer and a real number or floating points/doubles.

I want to only select the non-integers excluding complex numbers i.e. the reals (doubles/floating points in computer science) in my excel spreadsheet. How do I do this?

Answer 1

As Akina points out in the comments, we DO all understand the difference between them.

The real point though, is that YOU do not really understand something, a couple things actually though one is utterly on this point, about Excel:

For in-cell values there is no such thing as an integer type in Excel. ALL numbers, at ALL times, ALWAYS and in ALL circumstances and ALL conditions, are IEEE double precision type values. There isn't even a single "gotcha" situation in which that varies for you to win bar bets with. Under EVERY condition on God's green earth, that is what they are.

Bear in mind, for instance, that when Excel DISPLAYS a numerical character as a bare integer, it is just that: display. When it displays and ("mostly") treats them as text, it is just that: display and treating as. In actuality, they are still stored and utilizable in Excel's guts as full double precision values.

Worse yet, Excel uses bit operations for a great deal of its internal math and generates all the possible approximations that leads to. So take a value, 12 perhaps, and start subtracting 0.1 from it. Or more particularly, put 0.1 in a cell and add or subtract that cell value from it while then subtracting/adding (whichever is the opposite operation) the explicitly typed value back to it. At some point, you will see the value becomes, oh, say, 12.00000000000001. Yay Excel. So even something somehow "purified" to become integer-like would still be double precision and eventually, be wrong, with the right random mix of things done to it.

Because it never was and never, ever could have been, an integer.

So your desire can easily be met: Every last number you encounter in Excel is a non-integer.

You can, however, test each value for rounding to an integer out to a certain precision you might choose. Various ways to test for exactly that are readily available and even if you are very new to Excel, you likely suspect one of the ways from what sounds like programming experience on your part: Excel has an INT() function. It behaves differently than you might expect with negative values though, so using another function you'd probably expect to find, TRUNC() ("truncate") is the better way to do this. MOD() is also popular. Just use TRUNC() to truncate the test value at 0 decimal places and subtract a truncated version of the same value truncated to, say, 8-10 or so decimal places to catch any significant ("real") decimal portion and see if the result is 0. If so, define that as an integer and ignore it in whatever manner you prefer. Things failing pass your condition and can be selected.

It is not really what you ask for, but it simulates it: "I'm not a real Integer, but I do play one on TV. You may have seen me in..."

To collect them, you can put said formula in a column using a result of "" (close to a null: a blank) for anything meeting the Integer test above and a result of the value itself if it fails it. Copy the results into another column as values, not formulas, and sort said column. The blanks will sort to the end leaving you a contiguous list in the column of non-Integers that you can then use however you like. There are slicker ways to do it but that's a simple one and it sounds like you have very little Excel experience so simpler is better, eh?

If I have that wrong and you are on top of it, you might know how to write a VBA macro to do the testing and creation of an array of said values that you can then do with what you please. Everything doable with Excel can be done with VBA and almost always better, if you can go to the time and trouble. So if you have a grip on VBA, that's the gold standard.

But since there can't really be a circumstance in which you MUST have Excel create values in cells that are Integers, but rather you're figuring it MUST do that anyway, why wouldn't it, so you could swiftly use that fact for your sieve, you will be thrilled to know that no, Excel does not do anything handy like that. So you have to deal with some test that satisfies you for its accuracy and then use the results.

Seriously though, not even one single "gotcha" type tricky little-known unique circumstance exists in which a numerical value in Excel is not a double precision data type. They are never, ever, ever, Integers. Ever.

Now, that all said, I'd point out that VBA can create and use Integer type variables, etc. Though again, getting to the point of figuring which values to be such lets you know already if a number is desired in your selection or not, so why run all the way down that road rather than just far enough to accomplish your goal?

Finally, you can do a lot of things with other programs as most any today can read an Excel file and the rest can read a CSV you create in Excel. Load the records into a database (like Excel'a cousin, Access) and code the test in it, then convert those passing the test to an actual Integer data type and do your selecting there, though that, like all else, keeps coming back to the idea that by that time, you know which pass your test for selection and so why do all the other bother?

But don't bother looking for ready-made Integers in Excel. They simply do not, and cannot, exist in cells in an Excel spreadsheet.