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

: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

: