Solved

Excel Formatting test

Posted on 2016-10-20
26
54 Views
Last Modified: 2016-10-24
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
Comment
Question by:loftyworm
  • 13
  • 9
  • 4
26 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
=A1=INT(A1)

EDITED: TRUE & FALSE wrong way round
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
=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 31

Expert Comment

by:Rob Henson
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

Open 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)

Open in new window

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

by:loftyworm
Comment Utility
@ 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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You may place another piece of code on the same module and run it to calculate the formulas.

Sub ReCalculate()
Calculate
End Sub

Open 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.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

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

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

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
:( 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 10 total points
Comment Utility
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

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
@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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

Open in new window

0
 
LVL 11

Author Comment

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
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

Open 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.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 490 total points
Comment Utility
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

Open in new window

1
 
LVL 11

Author Comment

by:loftyworm
Comment Utility
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

by:loftyworm
Comment Utility
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 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome. Glad to help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now