I am entering both positive and negative numbers in a column and want a way to identify them without an extra column for example -100.03 or -250.1 or 100.015. in this example I am listing moneyline betting odds and want to use the .1 to identify inside distance and .03 to mean over 3 rounds, and .015 to mean over 1.5 rounds. This will give me the type of prop bet even though I just have the number entered without the need for an extra column to identify it. I was thinking about regular expressions but not sure.

1 Answer 1


You haven't provided any range structure. But the decimal-only portion of any number can be found this way:

=number - INT(number)

So if your numbers in question were in A2:A, the CF custom formula might look like this:

  • When i enter a negative number which most are. it returns .97 is there a workaround for this like maybe =SQRT(A2 ^2)-INT(SQRT(A2^2))=.03 or perhaps you have another solution? How would I write a custom conditional format function to color red all such cells from a range D3:D114? would I just insert D3:D114 in the position of A2 or would I have to use an array formula of some type.
    Jan 13, 2021 at 12:19
  • Again, you haven't provided any context. I assume that every negative number you enter doesn't return .97, for instance. However, applying a custom CF rule to range D3:D114, you can try this: =ABS(D3)-ABS(INT(D3))=.03 [You only need to include the first cell in a range within the formula; the rest is extrapolated from that.]
    – Erik Tyler
    Jan 13, 2021 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.