Extract Number from text

[[email protected]]

I have cells with data such as
0.5 ml
1ml
560 gm
373 milliliters

and need a function to extract the number part of these. I found a
formula that sort of works,
-----------------
Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com

'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If

If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next iCount


ExtractNumber = CLng(lNum)
End Function
-----------------------
However this formula seems to ignore decimal points and for example the
0.5 is returned as 5. All I want is a function to return just number
part and not the units. Any ideas?

-Andrew V. Romero

[Tom Ogilvy]

Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

''''''''''''''''''''''''''''''''''''''''''
'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) or _
Mid(sText, iCount,1) = "." Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If

Next iCount


ExtractNumber = CDbl(lNum)
End Function

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> I have cells with data such as
> 0.5 ml
> 1ml
> 560 gm
> 373 milliliters
>
> and need a function to extract the number part of these. I found a
> formula that sort of works,
> -----------------
> Function ExtractNumber(rCell As Range)
> Dim iCount As Integer, i As Integer
> Dim sText As String
> Dim lNum As String
>
> ''''''''''''''''''''''''''''''''''''''''''
> 'Written by OzGrid Business Applications
> 'www.ozgrid.com
>
> 'Extracts a number from a cell containing text and numbers.
> ''''''''''''''''''''''''''''''''''''''''''
> sText = rCell
>
> For iCount = Len(sText) To 1 Step -1
> If IsNumeric(Mid(sText, iCount, 1)) Then
> i = i + 1
> lNum = Mid(sText, iCount, 1) & lNum
> End If
>
> If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
> Next iCount
>
>
> ExtractNumber = CLng(lNum)
> End Function
> -----------------------
> However this formula seems to ignore decimal points and for example the
> 0.5 is returned as 5. All I want is a function to return just number
> part and not the units. Any ideas?
>
> -Andrew V. Romero
>

[HSalim[MVP]]

Try this:
First set a reference to Microsoft VBScript Regular Expressions 5.5
this will strip out the first numeric value including decimal, even if it is
in the middle of the string

so
GetNumber("-56,424.45 sldkfns")
GetNumber("-56,424.45sldkfns")
GetNumber("sagsadgag -56,424.45sldkfns")

will all return the same value -56424.45

HS
-----------------------------------------

Function GetNumber(stringVal As String) As Double

Dim regEx, Match, Matches ' Create variable.
Set regEx = New REGEXP ' Create a regular expression.
regEx.IgnoreCase = True ' Set case insensitivity.
regEx.Global = True ' Set global applicability.
Const patrn1 = "[0-9\.\,\-]+" 'look for any digit 0 to 9 or decimal
point or comma or the minus sign

regEx.Pattern = patrn1 ' Set pattern.

Set Matches = regEx.Execute(stringVal) ' Execute search.
If Matches.Count > 0 Then
GetNumber = CDbl(Matches(0).Value)
Else
GetNumber = 0
End If

Set Matches = Nothing
Set regEx = Nothing

End Function






<[email protected]> wrote in message
news:[email protected]...
:I have cells with data such as
: 0.5 ml
: 1ml
: 560 gm
: 373 milliliters
:
: and need a function to extract the number part of these. I found a
: formula that sort of works,
: -----------------
: Function ExtractNumber(rCell As Range)
: Dim iCount As Integer, i As Integer
: Dim sText As String
: Dim lNum As String
:
: ''''''''''''''''''''''''''''''''''''''''''
: 'Written by OzGrid Business Applications
: 'www.ozgrid.com
:
: 'Extracts a number from a cell containing text and numbers.
: ''''''''''''''''''''''''''''''''''''''''''
: sText = rCell
:
: For iCount = Len(sText) To 1 Step -1
: If IsNumeric(Mid(sText, iCount, 1)) Then
: i = i + 1
: lNum = Mid(sText, iCount, 1) & lNum
: End If
:
: If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
: Next iCount
:
:
: ExtractNumber = CLng(lNum)
: End Function
: -----------------------
: However this formula seems to ignore decimal points and for example the
: 0.5 is returned as 5. All I want is a function to return just number
: part and not the units. Any ideas?
:
: -Andrew V. Romero
:

[K Dales]

Just modify the IsNumeric line:
If IsNumeric(Mid(sText, iCount, 1)) Or (Mid(sText, iCount, 1)=".") Then...
--
- K Dales


"[email protected]" wrote:

> I have cells with data such as
> 0.5 ml
> 1ml
> 560 gm
> 373 milliliters
>
> and need a function to extract the number part of these. I found a
> formula that sort of works,
> -----------------
> Function ExtractNumber(rCell As Range)
> Dim iCount As Integer, i As Integer
> Dim sText As String
> Dim lNum As String
>
> ''''''''''''''''''''''''''''''''''''''''''
> 'Written by OzGrid Business Applications
> 'www.ozgrid.com
>
> 'Extracts a number from a cell containing text and numbers.
> ''''''''''''''''''''''''''''''''''''''''''
> sText = rCell
>
> For iCount = Len(sText) To 1 Step -1
> If IsNumeric(Mid(sText, iCount, 1)) Then
> i = i + 1
> lNum = Mid(sText, iCount, 1) & lNum
> End If
>
> If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
> Next iCount
>
>
> ExtractNumber = CLng(lNum)
> End Function
> -----------------------
> However this formula seems to ignore decimal points and for example the
> 0.5 is returned as 5. All I want is a function to return just number
> part and not the units. Any ideas?
>
> -Andrew V. Romero
>
>

[Ron Rosenfeld]

On 27 Dec 2005 09:56:32 -0800, [email protected] wrote:

>I have cells with data such as
>0.5 ml
>1ml
>560 gm
>373 milliliters
>
>and need a function to extract the number part of these.

If your cells always have the numeric part first, then you can use a fairly
simple UDF:

===========================
Function ExtractNum(rg) As Double
ExtractNum = Val(rg)
End Function
===========================

If the numeric part may not always be first, then:

==========================
Function ExtractNum(rg) As Double
Dim i As Long

For i = 1 To Len(rg)
If IsNumeric(Mid(rg, i, 1)) Then
ExtractNum = Val(Mid(rg, i, Len(rg) - i + 1))
Exit Function
End If
Next i
ExtractNum = ""
End Function
=============================


--ron