reading a string and only outputting the integers

[bglanton]

Hi I am trying to right a code that can read a set of strings that contains numbers and letters and I want to output only the numbers here is what i have

Function ExtractNumber(rCell As Range)
Dim iCount As Integer
Dim i As Integer
Dim sText As String
Dim lNum As Long


For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = CInt(Mid(sText, iCount, 1)) & lNum


If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))

Next iCount

ExtractNumber = lNum
End Function

 

[Michael M]

Try

Function ExtractNumber(rCell As Range)
Dim lCount As Long, l As Long, sText As String, lNum As String
sText = rCell
For lCount = Len(sText) To 1 Step -1
    If IsNumeric(Mid(sText, lCount, 1)) Then
        l = l + 1
        lNum = Mid(sText, lCount, 1) & lNum
    End If
If l = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next lCount
ExtractNumber = CLng(lNum)
End Function

 

[pike]

or this classic worksheet function from Ron Coderre where you dont need to use a UDF. just adjust the A3 cell to suit
=SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

or

Option Explicit
Function ExtractifNumber(rCell As Range)
    Dim iCount As Integer, lNum As Long, bln As Boolean
    For iCount = 1 To Len(rCell)
        If Mid(rCell, iCount, 1) Like "#" Then bln = True: lNum = lNum & Mid(rCell, iCount, 1)
    Next iCount
    ExtractifNumber = IIf(bln, lNum, "Nil")
End Function