[Solved] Extracting a Number from Strings

MartyMcFly

New member
Hi there, Can someone help me please ?

how do i extract a number if it has a text before it and a full stop after .... for example, the cell says "Money Out (£) 8.46." All i want is the numerical value of "8.46", please let me know, thanks in advance !

It could be to extract the number from the cell to a new cell or to replace the cell with just the number ? anyway is okay as i can move the value over afterwards
 
Hi there, Can someone help me please ?

how do i extract a number if it has a text before it and a full stop after .... for example, the cell says "Money Out (£) 8.46." All i want is the numerical value of "8.46", please let me know, thanks in advance !

It could be to extract the number from the cell to a new cell or to replace the cell with just the number ? anyway is okay as i can move the value over afterwards
Dear MartyMcFly

Welcome to ExcelDemy Forum! Thanks for reaching out and sharing your problem with such clarity.

Assuming you have a cell containing values like "Money Out (£) 8.46," you want the numerical value only. You need to extract numbers from cells and insert them into new cells (this can easily be done by applying Excel Formulas) or replace the cell values with numbers (this can quickly be done by running Excel VBA Code).

Don't worry! We have demonstrated your situation in a workbook and solved both problems.

SOLUTION Overview:

Extracting the number from the cell to a new cell or replacing the cell with just the number.gif

Excel Formulas:
=VALUE(MID(B5, FIND(") ", B5) + 1, FIND(".", B5, FIND(".", B5) + 1) - (FIND(") ", B5) + 1)))

Excel VBA Code:
Code:
Sub ReplacingCellValueWithNumber()
   
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim i As Long
   
    Set ws = ThisWorkbook.ActiveSheet
   
    Set selectedRange = Application.Selection
   
    If selectedRange.Columns.Count > 1 Then
        Exit Sub
    End If
       
    For Each cell In selectedRange
       
        If cell.Value = "" Then
            GoTo endOfALoop
        End If
                               
        Dim numText As String
        numText = Mid(cell.Value, InStr(cell.Value, ") ") + 1, InStr(InStr(cell.Value, ".") + 1, cell.Value, ".") - (InStr(cell.Value, ") ") + 1))
        cell.Value = CDbl(numText)
               
endOfALoop:
       
    Next cell
   
End Sub

I have attached the solution workbook for better understanding. Stay blessed.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy
 

Attachments

  • MartyMcFly (SOLVED).xlsm
    19.8 KB · Views: 1

Online statistics

Members online
0
Guests online
22
Total visitors
22

Forum statistics

Threads
318
Messages
1,408
Members
583
Latest member
mibr fan token
Top