Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel Formatting test

Posted on 2016-10-20
Medium Priority
102 Views
How can I test the formatted excel values, and not the exact values?

A1 = 123.45
It appears formatted as 123
If A1 = wholenumber (Exactly) then it will be FALSE
If A1 = wholenumber (appears as Formatted) then it will be TRUE
0
Question by:loftyworm
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 13
• 9
• 4

LVL 33

Expert Comment

ID: 41852409
=A1=INT(A1)

EDITED: TRUE & FALSE wrong way round
0

LVL 33

Expert Comment

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

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

LVL 11

Author Comment

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

LVL 11

Author Comment

ID: 41852565
=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.
0

LVL 33

Expert Comment

ID: 41852582
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.
0

LVL 32

Expert Comment

ID: 41852598
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
``````
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)
``````
The above function will return TRUE if the number 123.45 is custom formatted to show as 123, a whole number.
0

LVL 11

Author Comment

ID: 41852689
@ 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?
0

LVL 32

Expert Comment

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

``````Sub ReCalculate()
Calculate
End Sub
``````

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.
0

LVL 32

Expert Comment

ID: 41852710
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.
0

LVL 11

Author Comment

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

I added the code to the end, no effect.
0

LVL 32

Expert Comment

ID: 41852800
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.
0

LVL 11

Author Comment

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

LVL 11

Author Comment

ID: 41852811
:( 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
0

LVL 32

Expert Comment

ID: 41852850
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
0

LVL 33

Assisted Solution

Rob Henson earned 40 total points
ID: 41853781
See attached with some samples. Values in column being checked by formula in column F; values in column A are just showing what is actually in column C with 2 decimal places.

Also now showing a second option:

=A4-ROUND(A4,0)=0
or
=A4-ROUND(A4,0)<>0

Choice of above will depend on which is supposed to be True or False.

The cell formatting to zero decimals will show the same effect as the ROUND function. Therefore comparing actual value to rounded value is effectively comparing to formatted value.
Format-test.xlsx
0

LVL 11

Author Comment

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

LVL 11

Author Comment

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

LVL 11

Author Comment

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

LVL 32

Expert Comment

ID: 41853941
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
``````
0

LVL 11

Author Comment

ID: 41854009
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
0

LVL 11

Author Comment

ID: 41854050
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??
0

LVL 32

Expert Comment

ID: 41854060
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
``````
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.
0

LVL 32

Accepted Solution

Subodh Tiwari (Neeraj) earned 1960 total points
ID: 41854075
See the following code with the comments added so that you can understand the algorithm used in the function.
``````'The following function has only one argument i.e. a cell reference
Function IsFormattedAsWholeNumber(rng As Range) As Boolean
'Declaring the String variable to hold the number format of the cell
Dim fStr As String
'Checking if the cell is not blank
If rng.Value <> "" Then
'Checking if the number format of the cell is not the default "General" format
If rng.NumberFormat = "General" Then
'Checking if the cell contains a whole number and if so, the function will return True
If rng - Int(rng) = 0 Then IsFormattedAsWholeNumber = True
'If above condition is not met, the control passes to the Else statement
Else
'fStr String variable holds the custom number format of the cell
'So if the custom number format is whole number, fStr would be equal to 0
'And if the custom number format is a decimal number, fStr would be equal to 0.0 or 0.00 depending upon the decimal places
fStr = rng.NumberFormat
'Checking if the fStr which holds the custom number format as a string doesn't contain a . (period) in it
'That means the custom number format is 0 i.e. a whole number, hence the function would return True in this case
'And if this condition is not met, that means the custom number format string contains a . (period) in it indicating that the cell has a decimal number format
If InStr(fStr, ".") = 0 Then
IsFormattedAsWholeNumber = True
End If
End If
End If
End Function
``````
1

LVL 11

Author Comment

ID: 41854128
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......
0

LVL 11

Author Closing Comment

ID: 41857072
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 :(
0

LVL 32

Expert Comment

ID: 41857146
You're welcome. Glad to help.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
###### Suggested Courses
Course of the Month9 days, 19 hours left to enroll

#### 722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.