PDA

View Full Version : Extracting a number from text using a vba function



nirvehex
09-11-2014, 12:46 PM
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
09-11-2014, 01:28 PM
need to know what is the value of M3? The UDF must also deal with it.

Bob Phillips
09-11-2014, 01:44 PM
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
09-12-2014, 06:02 AM
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
09-12-2014, 07:29 AM
=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