SEARCH FORMULA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP
 
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
 
To leave out the -

Function gn(x As Range)
For i = 1 To Len(x) - 1
If IsNumeric(Mid(x, i, 1)) Then gn = gn & Mid(x, i, 1)
Next i
End Function
 
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
Click to expand...

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
 
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"))))
 
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 said:
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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

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
Click to expand...
Click to expand...
 
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"))))
Click to expand...

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
 
Thanks Rick


Gord

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 said:
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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top