Extracting a number from text using a vba function

[nirvehex]

Hi,

I have a formula:

=IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3)

This returns SPEC-8 or BID-10 or SPEC-4 or BID-1.5, ETC

All I want to do is put a function around the outside of this formula that extracts only the number.

So it would read 8 if the result of the inside formula was SPEC-8 or 1.5 if it was BID-1.5.

I'm wondering if there's a custom VBA function to do it that would look like this:

=ExtractNumber(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3))

Any ideas?

Thanks!

[SamT]

need to know what is the value of M3? The UDF must also deal with it.

[Bob Phillips]

Try

=IFERROR(IF(BJ3>Y3,M3,MID(VLOOKUP(F3, 'Bid Template'!L:CU,88,FALSE),FIND("-",VLOOKUP(F3, 'Bid Template'!L:CU,88,FALSE))+1,99)),M3)

[Kenneth Hobs]

Several ways to do it. In your case, taken literally, all those numbers would be negative. Luckily, some methods ignore the negative sign. Obviously, use GetNumber rather than your ExtractNumber or modify the UDF's name.
e.g.

Sub Test_GetNumber()  MsgBox GetNumber("BID-1.5")
End Sub
'Derk, ozgrid.com, 65763
Function GetNumber(s As String)
    Dim j As Long
    While Not IsNumeric(Left(s, 1))
        If Len(s) <= 1 Then
            Exit Function
        Else
            s = Mid(s, 2)
        End If
    Wend
    GetNumber = val(s)
End Function

[snb]

=F_snb(IFERROR(If(BJ3>Y3,M3,VLOOKUP(F3, 'Bid Template'!L:CU,88,FALSE)),M3))

Function F_snb(c00)
   F_snb=""
   if instr(c00,"-") then F_snb=split(c00,"-")(1)
End Function