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 |
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 |
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 |
All times are GMT +1. The time now is 05:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com