Remove everything but numbers from a cell

Question

I have an excel sheet where i use the follwoing command to get numbers from a cell that contains a form text:

=MID(D2;SEARCH("number";D2)+6;13)

It searches for the string "number" and gets the next 13 characters that comes after it. But some times the results get more than the number due to the fact these texts within the cells do not have a pattern, like the example below:

62999999990
21999999990
 11999999990 
6299999993) (
17999999999) 
 21914714753)
58741236714 P
 18888888820 

How do i avoid taking anything but numbers OR how do i remove everything but numbers from what i get?

Answer 1

You can user this User Defined Function (UDF) that will get only the numbers inside a specific cell.

Code:

Function only_numbers(strSearch As String) As String

    Dim i As Integer, tempVal As String

    For i = 1 To Len(strSearch)
        If IsNumeric(Mid(strSearch, i, 1)) Then
            tempVal = tempVal + Mid(strSearch, i, 1)
        End If
    Next

    only_numbers = tempVal

End Function

To use it, you must:

1. Press ALT + F11
2. Insert new Module
3. Paste code inside Module window
4. Now you can use the formula =only_numbers(A1) at your spreadsheet, by changing A1 to your data location.

Example Images:

• Inserting code at module window:

• Executing the function

Ps.: if you want to delimit the number of digits to 13, you can change the last line of code from:

    only_numbers = tempVal

to

    only_numbers = Left(tempVal, 13)

---

Alternatively you can take a look a this topic to understand how to achieve this using formulas.

Answer 2

If you are going to go to a User Defined Function (aka UDF) then perform all of the actions; don't rely on the preliminary worksheet formula to pass a stripped number and possible suffix text to the UDF.

In a standard code module as,

Function udfJustNumber(str As String, _
                       Optional delim As String = "number", _
                       Optional startat As Long = 1, _
                       Optional digits As Long = 13, _
                       Optional bCaseSensitive As Boolean = False, _
                       Optional bNumericReturn As Boolean = True)
    Dim c As Long

    udfJustNumber = vbNullString
    str = Trim(Mid(str, InStr(startat, str, delim, IIf(bCaseSensitive, vbBinaryCompare, vbTextCompare)) + Len(delim), digits))

    For c = 1 To Len(str)
        Select Case Asc(Mid(str, c, 1))
            Case 32
                'do nothing- skip over
            Case 48 To 57
                If bNumericReturn Then
                    udfJustNumber = Val(udfJustNumber & Mid(str, c, 1))
                Else
                    udfJustNumber = udfJustNumber & Mid(str, c, 1)
                End If
            Case Else
                Exit For
        End Select
    Next c

End Function

I've used your narrative to add several optional parameters. You can change these if your circumstances change. Most notable is whether to return a true number or text-that-looks-like-a-number with the bNumericReturn option. Note that the returned values are right-aligned as true numbers should be in the following supplied image.

By supplying FALSE to the sixth parameter, the returned content is text-that-looks-like-a-number and is now left-aligned in the worksheet cell.