Recognition of whole numbers

English
#1

I want to get a formula to recognise whole numbers and not any of the decimal numbers in a column of my spreadsheet - have been looking in documentation pages but have not found anything to help me.

Any suggestions gratefully received.

360749.333333333
360748.833333334
360749.166666667
360749.333333333
360749.666666667
360749 recognise, i.e. is selectable by formula to another column
360749.333333333
360749.5
360749.833333333
360749.166666667
360749.5
360749.666666667
360750 recognise, i.e. is selectable by formula to another column
360749.333333333
360749.666666667
360749.833333333
360750.166666666
360749.666666667
360750 recognise, i.e. is selectable by formula to another column
360750.166666666
360750.5
360749.833333333
360750.166666666
360750.333333333
360750.666666666
360750 recognise, i.e. is selectable by formula to another column
360750.333333333

2022-05-02
Hi - thanks for suggestions - I need to be clear - I wish to dynamically identify the whole numbers so that they are copied to another column by the formula in that new column, in the same row as they occur initially. I do not need to do anything to any of the decimal numbers - they should be ignored. I do want to round up, down or truncate ie. change the decimal numbers in any way. So the result will be as below - the four whole numbers will occur in the column next to the originating column and in the same row (please excuse the dashed line), the other rows in the new column will just remain blank. In fact there are many rows (about 9000) and many more whole numbers therefore. thanks -andy.

360749.333333333
360748.833333334
360749.166666667
360749.333333333
360749.666666667
360749-------------------------------360749
360749.333333333
360749.5
360749.833333333
360749.166666667
360749.5
360749.666666667
360750-------------------------------360750
360749.333333333
360749.666666667
360749.833333333
360750.166666666
360749.666666667
360750-------------------------------360750
360750.166666666
360750.5
360749.833333333
360750.166666666
360750.333333333
360750.666666666
360750--------------------------------360750
360750.333333333

#6

Do you need the recognition to be dynamic (a formula, for changing values)?

Do you need it in an additional column or in the same column?

If in an additional column, do you need to see the whole number or a hint (say, the word “whole”) or some (conditional) format change?

#12

02/05/22
See edit of my original question - thanks, andy

#2

Try these:
B1: =IF(INT(A1)=A1;A1;"")
B1: =IF(INT(A1)-A1;"";A1)
And more alphabetically:
B1: =IF(CEILING(A1)=A1;A1;"")
B1: =IF(FLOOR(A1)=A1;A1;"")
B1: =IF(MOD(A1;1);"";A1)
B1: =IF(ROUND(A1;0)=A1;A1;"")
Unnecessary arguments are omitted.

Choose the clearest or shortest one.

Edit:
B1: =IF(TRUNC(A1;0)=A1;A1;"")
INT() and TRUNС() differ in the way that int can also be used to convert numerical strings to integers.
NOTE: The second argument (zero) in the ROUND() and TRUNC() can be omitted.

Edit 2:
B1: =IF(QUOTIENT(A1;1)=A1;A1;"")

1 Like
#7

I double checked, all of these are safe for use with negative numbers.

However, what if the OP wants the column to have just the whole numbers with no intervening empty ("") cells, a truly compacted listing?

#8

Convert to values and sort.

#9

Well, I meant dynamically, and still listed in the order from the original data. :upside_down_face:

#10

Unlikely. The list contains duplicates, which means that the values are bound to records.

#11

The solution has been updated (TRUNC added).

#13

Thanks for your input - I have edited my question, -andy

#14

@andy-andyo, any of the proposed formulas will work for you. Mark it if the solution is obtained. And I don’t understand the point of your clarification. The point remains the same. Simply place the formula in each cell of the column.

Size does not matter.
int-formulas.ods (39.2 KB)

Use this:
B1: =IF(INT(A1)=A1;A1;"")

#15

Hi - misunderstood function - have tried TRUNC as your formula and it works perfectly as the decimal rows produce a blank while the whole number rows repeat the whole number. THanks very much for your help, much appreciated - andy

#3

The short solution without confusing IF function:

=A1*(INT(A1)=A1)
1 Like
#4

But my answer shows all (?) the possibilities. Multiply also by 0 the logical result of using any formula. However, in this case you have to hide the 0 in the cell. The OP didn’t want to take it out.

#5

An answer for static values (once for all):

  • Select the cells with the numbers
  • Find “.”
  • Uncheck [ ] Regular expressions
  • Replace al

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.