[Solved] Extracting a Number from Strings

[MartyMcFly]

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

 

[Lutfor Rahman Shimanto]

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:

​

Excel Formulas:

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

Excel VBA 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

 

[MartyMcFly]

Yes this works great
God bless you,
Thankyou

 

[Lutfor Rahman Shimanto]

Yes this works great
God bless you,
Thankyou

You are most welcome. We are glad the solution works perfectly.

Regards
ExcelDemy