There are two formulas that can help you to extract decimal value from a string, choose one of them as you need.
The formula that is irrespective of the sign of values:
Select a cell and type this formula =ABS(A1-TRUNC(A1)) (A1 is the cell you want to extract decimal value from) into the Formula Bar, and then press Enter key. Keep selecting the first result cell, drag fill handle down to get all results. You can see the decimal values are extracted only without sign as below screenshot shown.
The formula that reflect the sign of values:
Select a cell and type this formula =A1-TRUNC(A1) (A1 is the cell you want to extract decimal value from) into the Formula Bar, and then press Enter key. Keep selecting the first result cell, and drag fill handle down to get all results. You can see the decimal values are extracted with sign as below screenshot shown.
Easily extract decimal numbers from a selected range in Excel:
There is a VBA that can mass extract decimal values with the sign only from strings.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the pop-up window, click Insert > Module, then paste the following VBA code into the module.
VBA: Extract decimal values only
Dim xSRg As Range
Dim xDRg As Range
Dim xPRg As Range
Dim xSRgArea As Range
Dim xRgVal As String
Dim xAddress As String
Dim I As Long
Dim K As Long
Dim KK As Long
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xSRg = Application.InputBox("Please select range:", "KuTools For Excel", xAddress, , , , , 8)
If xSRg Is Nothing Then Exit Sub
Set xDRg = Application.InputBox("Select single cell:", "KuTools For Excel", , , , , , 8)
If xDRg Is Nothing Then Exit Sub
Set xDRg = xDRg(1)
For I = 1 To xSRg.Areas.Count
Set xSRgArea = xSRg.Areas.Item(I)
For K = 1 To xSRgArea.Count
xRgVal = xSRgArea(K).Value
KK = xSRgArea(K).Row - xSRg.Row
If IsNumeric(xRgVal) Then
xDRg.Offset(KK) = xRgVal - VBA.Fix(xRgVal)
3. Press the F5 key to run the code. Then a Kutools for Excel dialog box pops up, please select the range you want to extract decimal values only from, and click the OK button. See screenshot:
4. Then another Kutools for Excel dialog box pops up, you need to select a cell for locating the extracted decimal values. See screenshot:
You can see only the decimal values of specified numbers are extracted and placed on a certain range as below scrrenshot shown.
Easily extract decimal value with Kutools for Excel
If you don’t like the formula and VBA methods, here I introduce you a handy tool – Kutools for Excel. With its Extract Text utility, you can easily extract all decimal value from a range in Excel.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
WHAT IN THE WORLD DID I TRY TO DO?
by the way C9 was length of a audio file but had entered "18.75" value