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

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

Give us a few more examples of what you input can look like so we can better
advise
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"AshMorK" wrote in message
...
How can i extract just the numbers from a text cell??

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

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 InsertModule. 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

On Wed, 15 Nov 2006 12:27:02 -0800, AshMorK
wrote:

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