Re: Extract Number from text

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






<rrstudio2@xxxxxxxxxxx> wrote in message
news:1135706192.343932.58750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
: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
:


.

Relevant Pages

  • Re: Extract email addresses
    ... Because of a difference in the VBA flavor of Regular Expressions, ... Function REMid(str As String, Pattern As String, _ ... Dim objRegExp As RegExp ...
    (microsoft.public.excel.worksheet.functions)
  • Re: dividing an replacing spaces in string
    ... I knew regular expressions would help in this. ... This newly delimited string will dump into separate rows like this ... Dim colMatches As Object ... Set objRe = CreateObject ...
    (microsoft.public.excel.programming)
  • Re: Excel formula needed... regular expression?
    ... Function ExtrConcat(str As String, sPattern As String, _ ... 'Requires setting a Reference to Microsoft VBScript Regular Expressions 5.5 ... Dim mc As MatchCollection ... ExtrConcat = ExtrConcat & ma & sSeparator ...
    (microsoft.public.excel.misc)
  • Re: Regular Expressions in Access
    ... Public Function IsAlphaNumericAs Boolean ... Dim sTemp As String ... > unfortunately I can't remember whether I covered using regular expressions ...
    (microsoft.public.access.queries)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)