Remove everything except numbers from a cell
I have an excel sheet where I use the following command to get numbers from a cell that contains form text:
=MID(D2;SEARCH("number";D2)+6;13)
It looks for the string "number" and gets the next 13 characters that appear after it. But in some cases, the results get more than the number due to the fact that these texts inside the cells do not have a template, as in the example below:
62999999990
21999999990
11999999990
6299999993) (
17999999999)
21914714753)
58741236714 P
18888888820
How can I avoid anything but numbers, or how can I remove everything but numbers from what I get?
source to share
You can use this User Defined Function (UDF) which will only get 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)
in your spreadsheet, changingA1
to your data location.
Sample images
- Inserting code into the module window:
- Function execution
Ps: if you want to limit the number of digits to 13, you can change the last line of code:
only_numbers = tempVal
to
only_numbers = Left(tempVal, 13)
Alternatively, you can take a look at this section to understand how to achieve this using formulas.
source to share
If you're going to go to a custom function (aka UDF), follow the steps; do not rely on the preliminary worksheet formula to pass the split number and eventual suffix text in the UDF.
In the standard as code module,
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 used your story to add some optional parameters. You can change them if your circumstances change. Most notably, whether to return a true number or text with a similar appearance with the bNumericReturn option . Note that the returned values are right justified as the true numbers should be in the next image provided.
By supplying FALSE
the sixth parameter, the returned content is text, like -a-number, and is now left-aligned in the worksheet cell.
source to share