Create Account

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Q&A Forum

Number formatting update

Hello John. You've helped me earlier with a number formatting macro. It works good, but I noticed that when I have numbers with more decimals ( like 0.0355) it will only leave 2 (0.03). How can I modify it to keep all its decimals? Up to 5 decimals for example

Sub FormatByDP()

Dim Cl As Range, DP As Integer, DPsep As String, n As Long

DPsep = Application.DecimalSeparator ' get local decimal sepaartor (e.g. "." or ",")

'Loop through cells in range
For Each Cl In Range("F2:M1027")

    If IsNumeric(Cl.Value) And Not IsEmpty(Cl.Value) Then 
    
        n = n + 1 'count another cell
        
        With Cl
        
            .Value = WorksheetFunction.Round(.Value, 2) 
            
            If InStr(1, .Value, DPsep) > 0 Then ' see if there's a decimal separator
            
                DP = Len(.Value) - InStr(1, .Value, DPsep) ' see no. decimal places remaining
                
                Else
                
                DP = 0
                
            End If

            Select Case DP ' pick how to format the cell  based on DP
            
                Case 0
                    .NumberFormat = "0"
                Case 1
                    .NumberFormat = "0.0"
                Case Else
                    .NumberFormat = "0.00"

            End Select
            
        End With
        
    End If
    
Next Cl

MsgBox "Formatted " & n & " numbers in range"

End Sub

excel

posted by: Squishy (rep: 18) on Wed Dec 15, 2021 at 3:27 am

Answer

Discuss

Discussion

Hi Squishy.

The macro above deliberately formatted numbers with either 0, 1 or 2 since that was what you seemed to want. It also rounded all numbers to 2 decimal places (before finding how many dps to format the cell to) .

Do you want all numbers just formatted with up to 5 decimal places? (What are you doing with that formatting- just viewing it or operaring on it somehow?)

Please edit your question to clarify what you want.

Also, please don't address questions to me personally- questions should be open for anyone to reply (though I seem to be doing most answers at present!)

John_Ru (rep: 6142) Dec 15, '21 at 5:59 am

I want them just to be formatted to Number, but keep thier looks.
3 remains 3; 1.5 remains 1.5, 1.67 remains 1.67; 5.557 remains 5.557 and so on.
All the program works very well as it is right now. I just want to keep their decimals if they have more, because if I had a number like 5.7896 it would turn it into 5.78. I want to remain 5.7896 etc.

Squishy (rep: 18) Dec 15, '21 at 8:02 am

I've put my code like this

Sub FormatByDP()
 
Dim Cl As Range, DP As Integer, DPsep As String, n As Long
 
DPsep = Application.DecimalSeparator ' get local decimal sepaartor (e.g. "." or ",")
 
'Loop through cells in range
For Each Cl In Range("F2:M1027")
 
    If IsNumeric(Cl.Value) And Not IsEmpty(Cl.Value) Then
    
        n = n + 1 'count another cell
        
        With Cl
        
            .Value = WorksheetFunction.Round(.Value, 5)
            
            If InStr(1, .Value, DPsep) > 0 Then ' see if there's a decimal separator
            
                Cl.NumberFormat = "#####.#####" 'show up to 5 significant places
                
                Else
                
                Cl.NumberFormat = "0" ' don't show dp for integers
                
            End If
            
        End With
        
        n = n + 1 'count another cell
        
    End If
    
Next Cl
 
MsgBox "Formatted " & n & " numbers in range"
 
End Sub

But now numbers like "0.0039" remain ".0039" only the ones with 0 before "."

Squishy (rep: 18) Dec 17, '21 at 4:28 am

I'm looking at this on my phone and it makes little sense. Think my answer below does the extra decimal places (but can't check for a few hours), albeit showing them as Custom formatting though they will act as numbers.

Not sure why you object to the format label Custom (when the cells will act as numbers) or if I have any more time to spend on fixing your particular (unusual) desired outcome, sorry.

John_Ru (rep: 6142) Dec 17, '21 at 5:36 am

I'll wait for your response :)

Squishy (rep: 18) Dec 21, '21 at 6:27 am

My last paragraph was meant to overwrite the first. Unless you have a good reason to object to the Custom label, I won't waste any more time on this.

Note that if you ran my last macro, F8 would be set to Custom but if you put this in D8 say:

=ISNUMBER(F8)

it will return TRUE (i.e. Excel sees it as a number). Same with F14 (which displays 3 dps)

John_Ru (rep: 6142) Dec 21, '21 at 6:38 am

Add to Discussion

Answers

Selected Answer

Squishy

If you want to show integers without a decimal place and only up to 5 significant decimal places the revised code below (in the attached file) will do that.

Sub FormatTo5DPs()

Dim Cl As Range, DP As Integer, DPsep As String, n As Long

DPsep = Application.DecimalSeparator ' get local decimal sepaartor (e.g. . or ,)

'Loop through cells in range
For Each Cl In Range("F2:M29")
    If IsNumeric(Cl.Value) And Not IsEmpty(Cl.Value) Then 'not a blank and a number (or can be converted to a number)?
        With Cl
            '.Value = WorksheetFunction.Round(.Value, 5) ' reliably round to 5 dps
            If InStr(1, .Value, DPsep) > 0 Then
                Cl.NumberFormat = "#####.#####" 'show up to 5 significant places
                'Cl.NumberFormat = "0.00000" 'always show 5 decimal places
                Else
                Cl.NumberFormat = "0" ' don't show dp for integers
            End If
        End With
        n = n + 1 'count another cell
    End If
Next Cl

MsgBox "Formatted " & n & " numbers in range"

End Sub

Note that the first bold line above is commneted out (so rounding does NOT occur and the value in F20 remains as -250.399999999994 (even though that appears as -250.4). Also the second bold line means that non-integers will show as "Custom" formatting, not Numbers but you could comment that out and remove the apostrophe in front of the third line and you'll always get 5 significant places but ther formatting will show as Number (your call!).

Another option is to align decimal places in each column- integers will always have a decimal point but numbers will be aligned on that and show up to 5 significant places. To do that, replaces the With/End with section above with this:

        With Cl
            '.Value = WorksheetFunction.Round(.Value, 5) ' reliably round to 5 dps
            .NumberFormat = "?.?????" 'show decimal point and up to 5 significant places
        End With

Hope this helps

posted by: John_Ru (rep: 6142) on Wed Dec 15, 2021 at 3:11 pm

Discuss

Excel VBA Course - From Beginner to Expert

Number formatting update

Discussion

Answers

Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login