Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
Hi,
I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
I find it handy to use a User Defined Function.
Function DeleteNonNumerics(ByVal sStr As String) As Long 'returns numbers with decimal points if any 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(A1) Copy/paste the UDF above into a General Module in your workbook. If not familiar with macros and VBA, visit David McRitchie's website 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 your workbook. Gord Dibben Excel MVP On Sat, 20 Oct 2007 13:41:04 -0700, Michel wrote: Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
Sub getnum()
x = Range("a1") For i = 1 To Len(x) - 1 If IsNumeric(Mid(x, i, 1)) Or _ Mid(x, i, 1) = "-" Then mn = mn & Mid(x, i, 1) Next i MsgBox mn End Sub 'or put this in a regular module and use =gn(a1) if string is in cell a1 Function gn(x As Range) For i = 1 To Len(x) - 1 If IsNumeric(Mid(x, i, 1)) Or _ Mid(x, i, 1) = "-" Then gn = gn & Mid(x, i, 1) Next i End Function -- Don Guillett Microsoft MVP Excel SalesAid Software "Michel" wrote in message ... Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
On Sat, 20 Oct 2007 13:41:04 -0700, Michel
wrote: Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel You could use this UDF. It will return all of the digits in the string in the cell. You enter the formula: =reNums(str) into some cell where str is either the string containing some numbers or a cell reference. To enter the UDF in VBA, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens: ========================== Option Explicit Function reNums(str As String) Dim re As Object Const sPat As String = "\D" Set re = CreateObject("vbscript.regexp") re.Pattern = sPat re.Global = True reNums = re.Replace(str, "") End Function ======================== --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
Using built-in functions:
=LOOKUP(10^10,--MID(SUBSTITUTE(A1,"-",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4 ,5,6,7,8,9})),ROW(INDIRECT("1:255")))) -- Biff Microsoft Excel MVP "Michel" wrote in message ... Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
I think your function would be better off without the decimal points being
included in the pattern strings for the two Like comparison statements (otherwise text having periods in them, but with no digits, will return 0 instead of an easily checkable #VALUE! error). Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I find it handy to use a User Defined Function. Function DeleteNonNumerics(ByVal sStr As String) As Long 'returns numbers with decimal points if any 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(A1) Copy/paste the UDF above into a General Module in your workbook. If not familiar with macros and VBA, visit David McRitchie's website 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 your workbook. Gord Dibben Excel MVP On Sat, 20 Oct 2007 13:41:04 -0700, Michel wrote: Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
On Sat, 20 Oct 2007 22:39:37 -0400, "T. Valko" wrote:
Using built-in functions: =LOOKUP(10^10,--MID(SUBSTITUTE(A1,"-",""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4 ,5,6,7,8,9})),ROW(INDIRECT("1:255")))) -- Biff Microsoft Excel MVP That presumes that the only non-digit within the digit string is the hyphen, as was the case in the OP's single example. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
SEARCH FORMULA
Thanks Rick
Gord On Sun, 21 Oct 2007 02:14:15 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I think your function would be better off without the decimal points being included in the pattern strings for the two Like comparison statements (otherwise text having periods in them, but with no digits, will return 0 instead of an easily checkable #VALUE! error). Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . I find it handy to use a User Defined Function. Function DeleteNonNumerics(ByVal sStr As String) As Long 'returns numbers with decimal points if any 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(A1) Copy/paste the UDF above into a General Module in your workbook. If not familiar with macros and VBA, visit David McRitchie's website 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 your workbook. Gord Dibben Excel MVP On Sat, 20 Oct 2007 13:41:04 -0700, Michel wrote: Hi, I need to get a number out of a cell with text. How should I make the formula ? Eg. : A1 A2 (RESULT) hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841 Many thanks in advance! Michel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need help - search formula | Excel Worksheet Functions | |||
need help with a search formula | Excel Discussion (Misc queries) | |||
another formula search, if any | Excel Discussion (Misc queries) | |||
Search formula | Excel Worksheet Functions | |||
Search within formula | Excel Worksheet Functions |