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

## 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!