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`

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.