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.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

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

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.