Extract the decimal part of a number with Excel VBA by using these macros. The first macro was submitted by wellsrPRO community member Giancarlo.

The second macro is a function I created using Giancarlo’s macro as inspiration. In this macro, you can get the decimal and keep it in decimal form, or you can convert it to a whole number, like Giancarlo did in his original macro. The comments in the function itself explain this nicely.


Reader’s Note: This is the first article in a new series featuring macros submitted by my incredible wellsrPRO community members. These articles are similar to my Code Library articles in that they usually won’t contain an accompanying detailed tutorial.

If you’re an existing wellsrPRO member, don’t forget to submit your own macro to the wellsrPRO community using the Share My Macros button.

wellsrPRO users can automatically import this community submission directly into their spreadsheet. Just look for “Community Submissions” in the Auto-Import dropdown menu.


Extract Decimal From Number

Created by Giancarlo

Sub Extract_Decimal()
    'DEVELOPER:   Submitted by wellsrPRO community member "Giancarlo"
    'DESCRIPTION: This subroutine extracts the decimal values from numbers in Column B.
    '             It rounds the decimals to two decimal places, then it
    '             places the results 5 columns to the right.
    Dim x   As String
    With Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        x = .Address
        .Offset(, 5) = Evaluate("if(isnumber(" & x & "),round((" & x & "-int(" & x & ")),2)*100," & x & ")")
    End With
End Sub

This subroutine is very specific. It grabs the decimal values from numbers in Column B and rounds them to two decimal places. It then places the results 5 columns to the right.

That’s one thing I like about user submissions. They’re specific, but they’re also inspirational.


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free


Extract Decimal From Number Function

Inspired by Giancarlo's submission

I took Giancarlo’s macro and adapted it to a UDF. You can use it in an Excel formula to extract the decimals from a number in another cell.

Function GetDecimal(cell As Range, Optional ConvertToWhole As Boolean) As Variant
    'DEVELOPER:   Ryan Wells (wellsr.com)
    '             Inspired by wellsrPRO community member "Giancarlo"
    'DESCRIPTION: This function extracts the decimals from a number in Excel.
    'HOW TO USE:  1) The first argument should be a cell in your spreadsheet
    '             2) The second argument is optional.
    '                a) If it's ignored or set to FALSE, the function returns the decimal
    '                   in a form similar to "0.154"
    '                b) If it's set to TRUE, the function returns a whole number, like "154"
    Dim iLen   As Integer
    If IsNumeric(cell) Then
        If ConvertToWhole = True Then
            iLen = Len(Split(CStr(cell), Application.DecimalSeparator)(1))
            GetDecimal = Round(cell - Int(cell), iLen) * 10 ^ (iLen)
        Else
            GetDecimal = cell - Int(cell)
        End If
    Else
        GetDecimal = "Not a number"
    End If
End Function

The first argument should be a cell in your spreadsheet, like cell B2. The second argument is optional. If it’s ignored or set to FALSE, the function returns the decimal in a form like 0.154. If it’s set to TRUE, the function returns a whole number, like 154.

Your formula would look something like this

=GetDecimal(B2) 'extracts decimals

or

=GetDecimal(B2,TRUE) 'extracts decimals and converts to whole number

Reach out to my VBA Consulting page if you have questions and I’ll be happy to help you out!

Please share this article with your friends on Facebook, Twitter, and Google+.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products.

Check out more VBA Tutorials

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.

Excel VBA Store