get only the numbers from a cell

[outlawspeeder]

Looking for code to pull numbers from a cell that has text with the number. I have some users that do not understand that excel cannot do math on cells with text and numbers.

Thank

Column A
abc 33
~rdt 45
& 344 fkfk
ddd 23 ss
44 dd 33

Result
33
45
344
23
4433

 

[sandy666]

use Power Query

Column1		Number
abc 33		33
~rdt 45		45
& 344 fkfk		344
ddd 23 ss		23
44 dd 33		4433

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Number", each Text.Select([Column1], {"0".."9"})),
    TypeNum = Table.TransformColumnTypes(TS,{{"Number", type number}}),
    TSC = Table.SelectColumns(TypeNum,{"Number"})
in
    TSC

 

[Trevor G]

You could also try

Sub DeleteText()
Dim cell As Range
Dim ran As Range
On Error Resume Next
Set ran = Range("a2:a500") 'Suggested range
For Each cell In ran
    lOut = ""
    For i = 1 To Len(cell.Value)
        xTemp = Mid(cell.Value, i, 1)
        If xTemp Like "[0-9]" Then
            lStr = xTemp
        Else
            lStr = ""
        End If
        lOut = lOut & lStr
    Next i
    cell.Value = lOut
Next
End Sub

 

[jmacleary]

Hello there. There are a number of sites that have this answer, I copied this function from one:

Function GetNumeric(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

 

[outlawspeeder]

Trevor G

Thank you I can got where I need to be with what you provided

 

[Trevor G]

Happy to help and thank you for letting us know you have a working solution.