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

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

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

SEARCH FORMULA
 

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

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

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

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

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

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