Search code examples
regexexcelexcel-formulaudf

Remove everything but numbers from a cell

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?

Solution

  • 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:

    enter image description here

    • Executing the function

    enter image description here

    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.