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?

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

- Press ALT + F11
- Insert new Module
- Paste code inside Module window
- 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：

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.

### Answer：

If you don’t want VBA and would like to use Excel Formulas only, try this one:

```
=SUMPRODUCT(MID(0&MID(D2,SEARCH("number",D2)+6,13),LARGE(INDEX(ISNUMBER(--MID(MID(D2,SEARCH("number",D2)+6,13),ROW($1:$13),1))* ROW($1:$13),0),ROW($1:$13))+1,1)*10^ROW($1:$13)/10)
```

Tags: regexregex