Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting numbers from a text
How can i extract just the numbers from a text cell??
Example: Turn this: 000 234 -1 k -- into this: 2341 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting numbers from a text
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extracting numbers from a text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy from a Cell to a text box. | Excel Worksheet Functions | |||
Sum multiple cells with different numbers and text. | Excel Discussion (Misc queries) | |||
How can I use "VLOOKUP" with cells containing both Text & Numbers? | Excel Worksheet Functions | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |