Excel Formatting test

=A1=INT(A1)

EDITED: TRUE & FALSE wrong way round

Alternatively, you might need to use LEFT & ROUND instead:

=IFERROR(LEFT(A20,FIND(".",A20,1)-1)*1=ROUND(A20,0),TRUE)

Yikes
Can you explain this one a little for me please;
=IFERROR(LEFT(A20,FIND(".",A20,1)-1)*1=ROUND(A20,0),TRUE)

=A1=INT(A1)
this does not appear to work.

The number I am seeing 123 in cell A1 is correctly displayed with formatting, while the entered actual value is 123.45.
But the formula returns false.

The LEFT part takes the leftmost characters before a decimal point. This is compared to the 0 decimal point rounded value. If same then True. The IFERROR allows for cell value being a whole number so no decimal point to find.

You may use User Defined Function (UDF) to check if the number is formatted as a whole number.
To do this follow these steps....

1. Open your workbook and press Alt+F11 to open VB Editor.
2. On VB Editor --> Insert --> Module --> And paste the code given below into the opened code window.
3. Close the VB Editor.
4. Save your workbook as Macro-Enabled Workbook.

Now you are ready to use the function just like an inbuilt function.

Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
fStr = rng.NumberFormat
If InStr(fStr, ".") = 0 Then
   IsFormattedAsWholeNumber = True
End If
End Function

Select allOpen in new window

Now use the above function like this...
Assuming you want to check the Number format of the cell A1, then place the following formula in any cell on the sheet.

=IsFormattedAsWholeNumber(A1)

Select allOpen in new window

The above function will return TRUE if the number 123.45 is custom formatted to show as 123, a whole number.

@ Rob,
My testing for this is failing.  Are you sure this works against what is Displayed on the screen (formatting) and NOT the actual value?

@Subodh
This seems to work, but the macro will not calculate unless I select the formula and hit enter.
I will have 50+, can I make it just do it?

You may place another piece of code on the same module and run it to calculate the formulas.

Sub ReCalculate()
Calculate
End Sub

Select allOpen in new window

You may assign a shortcut key to this macro and use that shortcut key to run the code or you may also assign this code to a Form Control Button or a Shape as well and click to run the code.

Or you can manually calculate all the formulas on the Sheet by going to the Formula Tab and click on Calculate Sheet under Calculation Section on top right of the ribbon.

Yes, I tried the "Calculate Sheet" and it did not appear to work :(

I added the code to the end, no effect.

Calculate Sheet should work without an issue. Calculate Sheet is supposed to recalculate all the formulas on the sheet whether inbuilt or udf.
To check this, change the format of some numbers and then click on Calculate Sheet and see if the formulas dependent on those cells return the correct output.

Yep, that is my testing process.  It is not calculating them unless I select inside the cell, and press enter.

:( ugh.
it was working, and now is not at all.
I am getting this now
1.1                  TRUE  (no change no mater what)
1.1                  FALSE (change only on select enter)
1.1                  TRUE (no change no mater what)
                  
1.1                  TRUE
1                  FALSE
1.1                  TRUE

then
1.1                  TRUE
1                  TRUE
1.1                  TRUE

The function will work for the numbers with custom formatting. e.g. if you just input 1 in a cell, General formatting is applied to the cell, same is the case when you input 1.1 in a cell, still the formatting is general unless you manually custom format it as a Number with one decimal point.

Please refer to the attached.
CheckFormat.xlsm

So there is a difference in; No formatting, defined formatting, and default formatting??  In relation to the actual value and the displayed value?

Just a note to you both, this is a complicated question, and I want to say thank you both for your help and time!!

@Rob
In the example, If I change the Cell formatting to General, the Cell Displays 123.45 but the test still returns TRUE.

You may try the following UDF to check the format whether it is default General format or a custom number format applied to the cell.

Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
If rng.NumberFormat = "General" Then
   If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
Else
   fStr = rng.NumberFormat
   If InStr(fStr, ".") = 0 Then
      IsFormattedAsWholeNumber = True
   End If
End If
End Function

Select allOpen in new window

TY again.  I am still trying to get this to run.  I am having a more fundamental problem of the marco not running automatically.  I think I have narrowed this down to my testing process.  A change in the cell formatting to either general or decimal places, will not trigger the macro (even if told to calculate).  BUT if I change the field like 1.1 to 1.2 or copy and paste, the macro DOES run as designed.

As for the general formatting testing, the additional code is awesome :)
May I ask one file thing, this is returning 'last value' on a blank or null field, I need to return FALSE on blank values

I also need to expand this testing to dates and other stuff.   Rather then telling me, can you tell me perhaps how this macro is working?  This line does ...... and this value is from ......


What I am seeing now is
I am adding a MACRO to an excel document, essentially adding a new formula variable, or "Function"
So

Define the Function with
          Function IsFormattedAsWholeNumber(rng As Range) As Boolean
This is the function name, and the data it is expecting (What is this, (rng As Range)?)

Declare a variable ???
           Dim fStr As String (What is Dim short for?)

First IF
          If rng.NumberFormat = "General" Then
Object property test on incoming variable
Where can I find these to run my tests against??

Second IF
   If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True        
A calculation to see if the number is a whole number with the built in excel Function INT()  Can other macros I build call each other in the same way?

Set the value of the defined variable to one of the properties of the incoming data
           Else
                fStr = rng.NumberFormat

Third IF
           If InStr(fStr, ".") = 0 Then
                     IsFormattedAsWholeNumber = True
There is a test here I do not understand.
The excel function InStr(VAR,"character") checks to see if there is a period in the value???
Then set the function to return the value TRUE.

End the Function
         End If
       End If
     End Function
If all the tests fail, it should return FALSE?  How do we know this, and that it should not return something like a number, Y, Yes, N, or NO??
From reading, these are important to insure that the function runs when the values change. But this is not effected by formatting??

You may try this so that the following function will return False for blank cells.

Function IsFormattedAsWholeNumber(rng As Range) As Boolean
Dim fStr As String
If rng.Value <> "" Then
   If rng.NumberFormat = "General" Then
      If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
   Else
      fStr = rng.NumberFormat
      If InStr(fStr, ".") = 0 Then
         IsFormattedAsWholeNumber = True
      End If
   End If
End If
End Function

Select allOpen in new window

And again, after changing the number format of any cell if you click on Calculate Sheet, it will recalculate all the functions on the sheet.

This is AWESOME
I could not ask for more!

Do you have any comment on safety and security as far as limiting the macros to only run on very specific files?

Please give me a little time to work this out, and I will try to close this today......

TY to both Experts, they were very helpful!  But Rob's solution would not work in my testing.  It appears I will be forced to use MACRO's :(

You're welcome. Glad to help.