Extracting Number only from string of text

[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!

 

[Rick Rothstein]

See if this formula gives you want you want (I have already incorporated the formula you posted into it)...

=TRIM(RIGHT(SUBSTITUTE(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3),"-",REPT(" ",99)),99))

 

[Vidar]

Try this:
=MID(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3),SEARCH("-",IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3))+1,99)+0
The +0 at the end convert the value from text to number if you need o do calculations With it afterwards.
Skip +0 if you want the result to be formatted as text.

 

[nirvehex]

Both formulas almost worked, except sometimes I have "BID-.48" and they both turned this number into 19389.51. I just want it to turn into .48. For all the whole numbers it worked fine though. Any ideas?

 

[Rick Rothstein]

Both formulas almost worked, except sometimes I have "BID-.48" and they both turned this number into 19389.51. I just want it to turn into .48. For all the whole numbers it worked fine though. Any ideas?

You apparently did not see the formula I posted in Message #2 yet as I do not think it could be returning such a value.

By the way, it is always a good idea to post who you are replying to when you have more than one response.

 

[nirvehex]

You apparently did not see the formula I posted in Message #2 yet as I do not think it could be returning such a value.

By the way, it is always a good idea to post who you are replying to when you have more than one response.

I did see your message. However, it was my fault, I had a vlookup in pulling off of another tab. My apologies

 

[Rick Rothstein]

I did see your message. However, it was my fault, I had a vlookup in pulling off of another tab. My apologies

So did the formula work for you then?