Extracting numbers from a text

[Guest]

How can i extract just the numbers from a text cell??

Example: Turn this: 000 234 -1 k --> into this: 2341

 

[Bernard Liengme]

Give us a few more examples of what you input can look like so we can better
advise
best wishes

 

[Gord Dibben]

You could employ a user defined function.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

usage is: =deletenonnumerics(cellref)

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula in any cell as shown above.


Gord Dibben Excel MVP

How can i extract just the numbers from a text cell??

Example: Turn this: 000 234 -1 k --> into this: 2341

Gord Dibben MS Excel MVP