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's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
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
Your formula would look something like this
=GetDecimal(B2) 'extracts decimals
=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.