Read a cell's value and extract the number that it is contained

Question

The cells of a specific range contain information of the form Check. 3 months or Dep. 2 months. I need embed vba so to read these cells and every time extract, that is output, the number that it is contained.

Edit: I know that there are functions when you know what you're searching for but for what I describe I am not aware of anything

Can you help please as I haven't encounter a similar task in the past.

Use [REGEX](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Scott Craner, Mar 02 '16 at 17:57
Or if there are always spaces surrounding the numbers you can you the Split Function then cycle through the array till you find the number and return that. — Scott Craner, Mar 02 '16 at 18:02

Sorceri · Answer 1 · 2016-03-02T18:08:39.057

You can loop through each character in the string and check to see if it is numeric. this will work for values 0-9. If there are values higher you would need to adjust this to look for multiple digits and not exit once the first one is found.

Public Function GetMyDigit(cell As Range) As Integer
Dim s As String
Dim i As Integer

'get cell value
s = cell.Value

'loop through the entire string
For i = 1 To Len(s)
    'check to see if the character is a numeric one
    If IsNumeric(Mid(s, i, 1)) = True Then
        'set the function to the value and exit
        GetMyDigit = Mid(s, i, 1)
        Exit Function
    End If
Next i
End Function

and if you have multiple digits

Public Function GetMyDigit(cell As Range)
Dim s As String
Dim i As Integer
Dim answer
'get cell value
s = cell.Value

'loop through the entire string
For i = 1 To Len(s)
    'check to see if the character is a numeric one
    If IsNumeric(Mid(s, i, 1)) = True Then
        'set the function to the value and exit
        answer = answer & Mid(s, i, 1)
    End If
Next i
 GetMyDigit = answer
End Function

I already checked it as correct and voted as helpful, as I've already run it for various input/cells and it works perfectly!! Well done Sorceri, many thanks! — GunnRos86, Mar 02 '16 at 18:12
FWIW, If any of the numbers have decimal places then this will remove the decimal marker, as in 3.5 will become 35. But I agree this is a great method. — Scott Craner, Mar 02 '16 at 18:15

score 1 · Answer 2 · answered Mar 02 '16 at 18:11

1

Just to give another method:

Function findNumber(inPtStr As String) As Double
    Dim strArr() As String
    Dim i As Long
    strArr = Split(inPtStr)
    For i = LBound(strArr) To UBound(strArr)
        If IsNumeric(strArr(i)) Then
            findNumber = --strArr(i)
            Exit Function
        End If
    Next i
End Function

answered Mar 02 '16 at 18:11

Scott Craner

148,073
10
49
81

@user3333331 Just so you know, you can only mark one as correct. Mark the one you use. If you used sorceri then keep his as the correct one, If you used mine then leave mine as marked. It will not hurt our feelings if you choose the other. – Scott Craner Mar 02 '16 at 19:46

Read a cell's value and extract the number that it is contained

2 Answers2